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.
Dear Community,
Good day!
Here is my raw data with YTD andLYTD measure.
TotalYTD = TOTALYTD(SUM(CP[Value]),'Calendar'[Date])
TotalLYTD = CALCULATE([TotalYTD],SAMEPERIODLASTYEAR('Calendar'[Date]))
ChangeYoY = DIVIDE([TotalYTD] - [TotalLYTD],[TotalLYTD],0)
The figure is work fine when there have no month filter. But when its come to month filter,
The number is run, suppose current year is 13659.16 while last year is 19061.32. 40.50K looks like accumulated.
What should I do in order to get the excatly year to year and month to month number correctly when month and year filter applied?
Any help will appreciated.
Thanks
Solved! Go to Solution.
@NickProp28 , The number is correct for YTD. Maybe you need MTD vs Last year MTD
TotalLYTD = TOTALMTD(SUM(CP[Value]),dateadd('Calendar'[Date],-1,year))
TotalYTD = TOTALYTD(SUM(CP[Value]),'Calendar'[Date])
Please find the file, The YTD values are correct. If you need only MTD, use the MTD formula
MTD is not added to the file
@NickProp28 , Try last year like
TotalLYTD = TOTALYTD(SUM(CP[Value]),dateadd('Calendar'[Date],-1,year))
As I doubt sample period last year will continue in past
Dear @amitchandak ,
Thanks for the reply.
Have revised the code , but result is still the same.
Here is the table in all month, result look like accumulated.
Kindly advise
@NickProp28 , 40K for YTD for Feb 2020, Is what it should give.
Make sure you are using the column from the date table in the slicer and table.
Can you share sample data and sample output in table format? Or a sample pbix after removing sensitive data.
Dear @amitchandak ,
Here's the sample pbix: https://ufile.io/9ihptdbq
What I looking for is when I filtered on months 'Feb' and year '2020', the card should show current in '4165.80' while past year is '2178.80'.
Appreciate your help.
@NickProp28 , The number is correct for YTD. Maybe you need MTD vs Last year MTD
TotalLYTD = TOTALMTD(SUM(CP[Value]),dateadd('Calendar'[Date],-1,year))
TotalYTD = TOTALYTD(SUM(CP[Value]),'Calendar'[Date])
Please find the file, The YTD values are correct. If you need only MTD, use the MTD formula
MTD is not added to the file
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 |
---|---|
110 | |
94 | |
82 | |
66 | |
58 |
User | Count |
---|---|
151 | |
121 | |
104 | |
87 | |
67 |