Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi all,
Prior year = VAR CurrentMonth = SELECTEDVALUE( Dates[Month Name] ) VAR CurrentYear = SELECTEDVALUE( Dates[Year] ) RETURN CALCULATE( sumx('All Companies','All Companies'[@Profit]), FILTER( ALL( Dates ), Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1))
This works other than the fact that it doesn't show column subtotals for some reason. Does anyone have any ideas on how I can fix this?
if(HASONEVALUE(Dates[Fiscal Month]),CALCULATE( [Total GP],
FILTER( ALL( Dates ),
Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1)),SUMX(VALUES(Dates[Fiscal Month]),CALCULATE( [Total GP],
FILTER( ALL( Dates ),
Dates[Month Name] = CurrentMonth && Dates[Year] = CurrentYear - 1))
Thanks!
Solved! Go to Solution.
@EitanKGCS , Try new measure like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
That should give a year behind total
@EitanKGCS , Try new measure like
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),SAMEPERIODLASTYEAR('Date'[Date]))
That should give a year behind total
@amitchandak thank you for the idea but it hasn't worked.
I tried this:
@EitanKGCS , Date table should have all dates(Marked as date table) , You join should be single directional
few other reasons
Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
@amitchandak I see! I added in your formula and changed the date table to single and it works! I have to reorganise most of my filter slicers and other filters but at least I'm getting correct figures now.
Thanks so much!
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
23 | |
22 |