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.
Hi All,
In a one table I have to present YTD, LY YTD, and FY Last Year Figures.
For YTD and LY YTD figure it's pretty simple, as I am using TTOTAL YTD Formula:
YTD = TOTALYTD(sum(Facts[Value]),'Calendar'[Date])
YTD LY = CALCULATE([YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))
How to create measure to display FY LY next to that?
For example for data like that:
Year | Period | Value |
2015 | 1 | 10 |
2015 | 2 | 10 |
2015 | 3 | 10 |
2015 | .. | 80 |
2015 | 12 | 10 |
2016 | 1 | 20 |
2016 | 2 | 20 |
2016 | 3 | 20 |
2016 | .. | 160 |
2016 | 12 | 20 |
I want to se result like that:
Year | Period | CY YTD | LY YTD | FY LY |
2016 | 3 | 60 | 30 | 120 |
Thanks,
DL
Solved! Go to Solution.
The issue I had was with sorting field.
I had Month Name as Slicer, which was sorted but Month Number.
Wnen I was using ALL(Month Name) It was not working, but by adding ALL(Month Name, Month Number) to my formula, then the slicer was finally ignored something like that:
FY LY = CALCULATE(SUM(Facts[Value]),SAMEPERIODLASTYEAR('Calendar'[Date]),ALL(Period, Period sort))
Thank you everyone for replying.
Regards,
D.
The issue I had was with sorting field.
I had Month Name as Slicer, which was sorted but Month Number.
Wnen I was using ALL(Month Name) It was not working, but by adding ALL(Month Name, Month Number) to my formula, then the slicer was finally ignored something like that:
FY LY = CALCULATE(SUM(Facts[Value]),SAMEPERIODLASTYEAR('Calendar'[Date]),ALL(Period, Period sort))
Thank you everyone for replying.
Regards,
D.
TOTALYTD has a parameter field to specify a FY end date. So you can do this:
FY LYTD = TOTALYTD ( SUM(Facts[Value] ), SAMEPERIODLASTYEAR( Calendar[Date] ), "6/30" )
Thanks Mattbrice,
Your solution is good for MAT (moving annual total) bo it is not showing FY LY figures.
I have solved my issue by using parametrs for year and period and based on that I am calculating from-to date and by using FILTER I am able to get desired result, however I am looking for more dynamic solution...
Regards,
dariusz
Hi @lewand03,
If date function not suitable for your requirement, you can try to manually calculate the date range, then use them as part of filter.
Sample:
LYTD = VAR current_date = MAX ( table[date] ) RETURN CALCULATE ( SUM ( table[amount] ), FILTER ( ALLSELECTED ( table ), [Date] >= DATE ( YEAR ( current_date ) - 1, 1, 1 ) && [Date] <= DATE ( YEAR ( current_date ) - 1, MONTH ( current_date ), DAY ( current_date ) ) ) )
BTW, I don't think you can convert current date to accurate FY date, you only need to care about to Fiscal month.
Regards,
Xiaoxin Sheng
Thanks Xiaoxin,
YTD with "sameperiodlastyear" is working perfectly fine for me. I'd like to present FY LY dynamically (so independent on selected period). Your solution works in similar way to selection as a parameter.
BTW
I put it as a new idea to have TOTALFY formula as well, so if you also miss you please vote: link
Thanks,
D.
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 |