CODEWITHSUNDEEP
powerbidax
Adnan Tamimi
Adnan Tamimi

Reputation: 121

Why is this calculated column not taking the row context?

I am trying create a function for the following task.

I have a table (named Hyperion) with data in the following format:

Year Period Account Amount Entity
2022 P01 1234 2000 E1
2022 P02 1233 4000 E2
2023 P01 1236 1000 E3

I need to find the Amount (for some Account = A ) for last 3 Periods. That is, if I select year 2023 and period P01 from pivot filter, I get the amount sum of P11,P12 of year 2022 and P01 of year 2023. Similarly if I select year 2023 and period P03 from pivot filter, I get the amount sum of P01,P02,P03 of year 2023

Assumption : Hyperion table contains data for current year and last year only.

= var maxyear = MAX(Hyperion[Year])  
var selectedperiod = Hyperion[Period]

var start_periodYear = SWITCH(selectedperiod,
"P01", "P11"&"-"&(VALUE(maxyear)-1),
"P02", "P10"&"-"&(VALUE(maxyear)-1),
"P03", "P01"&"-"&VALUE(maxyear),
"P04", "P02"&"-"&VALUE(maxyear),
"P05", "P03"&"-"&VALUE(maxyear),
"P06", "P04"&"-"&VALUE(maxyear),
"P07", "P05"&"-"&VALUE(maxyear),
"P08", "P06"&"-"&VALUE(maxyear),
"P09", "P07"&"-"&VALUE(maxyear),
"P10", "P08"&"-"&VALUE(maxyear),
"P11", "P09"&"-"&VALUE(maxyear),
"P12", "P10"&"-"&VALUE(maxyear)
)

var start_pd =LEFT(start_periodYear , LEN(start_periodYear )-SEARCH("-",start_periodYear)-1)
var start_yr =RIGHT(start_periodYear , LEN(start_periodYear )-SEARCH("-",start_periodYear))

var end_yr = maxyear
var end_pd = selectedperiod
var entity = Hyperion[ENTITY]
var account = Hyperion[ACCOUNT]

var mCOGS = CALCULATE([COGS Act], FILTER(Hyperion, Hyperion[ENTITY]=entity && Hyperion[ACCOUNT]=4110011 && Hyperion[Period]>=start_pd && Hyperion[Period]<=end_pd && Hyperion[Year]>=VALUE(start_yr) && Hyperion[Year]<=VALUE(end_yr)))
return mCOGS

When I run this I get the total sum repeated in each row of the calculated column. It seems like dax is not evaluating the calculated column for each row. My calculation is wrapped inside the calculate function so it should be evaluated in row context I believe. What am I doing wrong ?

Upvotes: 0

Views: 281

Answers (1)

pauliec
pauliec

Reputation: 451

  1. In Excel, go to Data tab/Queries and Connections
  2. Open Power Query Editor
  3. Select Advanced Editor from the Home tab
  4. Identify the code you're currently using
  5. Try it with the below
let
    maxYear = List.Max(Hyperion[Year]),
    selectedPeriod = List.Single(Hyperion[Period]), // Use List.Single if you expect only one value in the list, otherwise use List.First to get the first value
    start_periodYear = 
        if selectedPeriod = "P01" then "P11" & "-" & Number.ToText(maxYear - 1)
        else if selectedPeriod = "P02" then "P10" & "-" & Number.ToText(maxYear - 1)
        else if selectedPeriod = "P03" then "P01" & "-" & Number.ToText(maxYear)
        // Add the rest of the conditions for "P04" to "P12"
        else null,

    startPosition = Text.PositionOf(start_periodYear, "-"),
    start_pd = Text.Start(start_periodYear, startPosition),
    start_yr = Text.End(start_periodYear, Text.Length(start_periodYear) - startPosition - 1),

    end_yr = maxYear,
    end_pd = selectedPeriod,
    entity = Hyperion[ENTITY],
    account = Hyperion[ACCOUNT],

    filteredRows = Table.SelectRows(Hyperion, each [ENTITY] = entity and [ACCOUNT] = 4110011 and [Period] >= start_pd and [Period] <= end_pd and [Year] >= Number.FromText(start_yr) and [Year] <= Number.FromText(end_yr)),
    mCOGS = List.Sum(filteredRows[COGS Act])
in
    mCOGS

Upvotes: 0

Related Questions