Wrong Total of Measure and Measure for the previous year

[liselotte]

Hi, I have a dimenstion date table dim_Date with column [Date] and a fact table fact_t with duplicated rows of same [Device], [Date], [Consumption] for many devices (due to other columns in this fact table which make rows unique, I couldn't remove duplicated rows for the above columns). I have a measure of calculating the Total Consumption of each device as follows:

TTL_Consumption = CALCULATE(SUMX(DISTINCT('fact_t'[Device]),FIRSTNONBLANK('fact_t'[Consumption],0)), FILTER('fact_t', 'fact_t'[Is Active Device]=1))

This works well in a visual table of columns 'fact_t'[Device], 'dim_Date'[Date], [TTL_Consumption]. But in the Total row of the visual table, it shows one of the values of TTL_Consumption in rows instead of Total Sum of all TTL_Consumption in rows. 

Moreover, when I want to calculate the TTL_Consumption for the previous year (from the same date of last year until the day before the date) with the query:

TTL_Consumption_prevyear = CALCULATE([TTL_Consumption], DATESBETWEEN('dim_Date'[Date], SAMEPERIODLASTYEAR('dim_Date'[Date]), ENDOFMONTH(PREVIOUSMONTH('dim_Date'[Date]))))

it gives an error of "SAMEPERIODLASTYEAR expects a contiguous selection ... bidirectional connection" even though it is 1-n single direction from dim_Date to fact_t. 

I've tried to change it to

TTL_Consumption_prevyear = CALCULATE( [TTL_Consumption],REMOVEFILTERS('dim_Date'[Date]), DATESBETWEEN('dim_Date'[Date], EDATE(MAX('dim_Date'[Date]),-12) ,MAX('dim_Date'[Date])-1))

It still doesn't work. Could anyone help me how to solve thoes issues, please?

[marcelsmaglhaes · Accepted solution]

Hey @liselotte ,

To address the issues you described, let’s go through each one separately:

1. Incorrect Total in the TTL_Consumption Measure

The problem with the total in the TTL_Consumption measure is likely due to the use of FIRSTNONBLANK, which is selecting only a single value instead of summing all values in the total. To fix this, try simplifying the measure to ensure it sums all consumptions of active devices. Here’s a potential solution:

TTL_Consumption =
CALCULATE(
SUMX(
'fact_t',
'fact_t'[Consumption]
),
'fact_t'[Is Active Device] = 1
)

2. Measure for Previous Year’s Consumption (TTL_Consumption_prevyear)

To calculate the consumption for the previous year correctly, consider using SAMEPERIODLASTYEAR or PARALLELPERIOD to ensure the year filter is applied properly. Here’s a revised version of the measure:

TTL_Consumption_prevyear =
CALCULATE(
[TTL_Consumption],
SAMEPERIODLASTYEAR('dim_Date'[Date])
)

If SAMEPERIODLASTYEAR continues to throw errors, you can use DATEADD to shift the date back by one year:

TTL_Consumption_prevyear =
CALCULATE(
[TTL_Consumption],
DATEADD('dim_Date'[Date], -1, YEAR)
)

These adjustments should correct the incorrect total in the visual and allow for proper calculation of the previous year’s consumption.

[liselotte]

Thank you. I changed my measure to 

TTL_Consumption = CALCULATE(SUMX(SUMMARIZE('fact_t','fact_t'[Device],'fact_t'[Consumption]),'fact_t'[Consumption]), FILTER('fact_t', 'fact_t'[Is Active Device]=1))

then everything works now. 🙂