Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
I am trying to create a bar chart with sum fact this year versus sum fact for the same period last year, based on a filter date range set by the user.
I was able to get it working using a separate measures table with calculated aggregations, but this meant that the user couldn't filter on any other facts in the data table, without breaking the measures table.
I have taken the calculated measures back into the data table, and am using a contiguous date table. Essentially the table below gives me the data I want for my chart from July 2016 to June 2017; but I can only get it here using the ALLEXCEPT feature, which gives me all periods, not the ones the user has filtered for.
Total Cost LY = CALCULATE( [Total Cost], SAMEPERIODLASTYEAR('Dates Table'[Calendar Date].[Date]),ALLEXCEPT('Dates Table','Dates Table'[Calendar Date]))
Solved! Go to Solution.
HI @gary_connolly,
have you tried to have the ALLSELECTED something like this:
Total Cost LY = CALCULATE ( [Total Cost], SAMEPERIODLASTYEAR ( 'Dates Table'[Calendar Date].[Date] ), ALLSELECTED ( 'Dates Table'[Calendar Date] ) )
Or then use the DATEADD function:
Total Cost LY =
CALCULATE (
[Total Cost],
DATEADD ( ALLSELECTED ( 'Dates Table'[Calendar Date] ), -1, YEAR )
)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHi @gary_connolly,
Why don't you try to use this formula:
Total Cost LY = CALCULATE ( [Total Cost], DATEADD ( 'Dates Table'[Calendar Date], -1, Year) )
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsHI @gary_connolly,
have you tried to have the ALLSELECTED something like this:
Total Cost LY = CALCULATE ( [Total Cost], SAMEPERIODLASTYEAR ( 'Dates Table'[Calendar Date].[Date] ), ALLSELECTED ( 'Dates Table'[Calendar Date] ) )
Or then use the DATEADD function:
Total Cost LY =
CALCULATE (
[Total Cost],
DATEADD ( ALLSELECTED ( 'Dates Table'[Calendar Date] ), -1, YEAR )
)
Regards,
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em PortuguêsUnfortunately when I use the Select function it means if I use any other Filters the chart errors
Hi @gary_connolly,
Why don't you try to use this formula:
Total Cost LY = CALCULATE ( [Total Cost], DATEADD ( 'Dates Table'[Calendar Date], -1, Year) )
Regards
MFelix
Regards
Miguel Félix
Proud to be a Super User!
Check out my blog: Power BI em Portuguêswill try the add date again, it's clear the issue i was having was contigious dates however, because when I converted the dates to EOMONTH and joined that to a date table with month end dates it worked. Not the most elegant solution but good enough for now.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
111 | |
100 | |
80 | |
64 | |
58 |
User | Count |
---|---|
146 | |
110 | |
93 | |
84 | |
67 |