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 everyone,
I need to compare TotalSale YTD vs TotalSale Last Year YTD.
For exmple, if today is Jan 18 2022, then I would like to compare TotalSale this year from 1/1/2022 to 1/17/2022 to TotalSale Last Year from 1/1/2021 to 1/17/2021.
My mesure are:
TotalSale YTD = TOTALYTD ([Totalsale], DATESYTD (CALENDAR[Date])
TotalSale LY YTD = CALCULATE ([TotalSale YTD], SAMEPERIODLASTYEAR (CALENDAR[Date])
Thank you
The issue that I have is when I apply these measures above to te metric (regions as rows and date as columns) , the TotalSale LY YTD would be the Sum of whole last year and TotalSale YTD shows the right number for current year but it shows the total for last year
Solved! Go to Solution.
Hi @Harry_Tran ,
I created some data:
Here are the steps you can follow:
1. Create measure.
TotalSale YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY()),1,1)&&'Table'[Date]<TODAY()))
TotalSale LY YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY())-1,1,1)&&'Table'[Date]<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi I have a accounting period date column from which I have month as a slicer
Need to show Current year YTD and whole Previous Year YTD both in a single table
Current Year YTD should be filtered by month and Previous Year YTD should ignore the month slicer selection and it should show Previous year YTD for the whole year.
For example:
Month | Current year YTD | Previous Year YTD |
Jan | 10 | 5 |
Feb | 10 | 5 |
Mar | 10 | 5 |
Apr | 10 | 5 |
May | 10 | 5 |
Jun | 10 | 5 |
Jul | 10 | 5 |
Aug | 10 | 5 |
Sep | 10 | 5 |
Oct | 10 | 5 |
Nov | 10 | 5 |
Dec | 10 | 5 |
Results expected:
Month - July
Current year YTD - 70 (From Jan to July)
Previous year YTD - 60 From (Jan to December)
Dax used :
Please help me with revised Dax
Hi @Harry_Tran ,
I created some data:
Here are the steps you can follow:
1. Create measure.
TotalSale YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY()),1,1)&&'Table'[Date]<TODAY()))
TotalSale LY YTD =
CALCULATE(SUM('Table'[rand]),FILTER(ALL('Table'),'Table'[Date]>DATE(YEAR(TODAY())-1,1,1)&&'Table'[Date]<DATE(YEAR(TODAY())-1,MONTH(TODAY()),DAY(TODAY()))))
2. Result:
Best Regards,
Liu Yang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly
Hi,
ENsure that the Calendar Table goes only till today, not till December 31, 2022.
Hi @Harry_Tran
I suggest you follow the approach discussed in this article:
https://www.sqlbi.com/articles/hiding-future-dates-for-calculations-in-dax/
The short description is:
1. Add a DatesWithSales column to your CALENDAR TABLE (adjust column names as required):
DatesWithSales =
'CALENDAR'[Date] <= MAX ( Sales[Date] )
2. Rewrite any time intelligence measures in this sort of form, using TotalSale LY YTD as an example:
(I have put all the date shifting in one measure rather than referencing TotalSale LY)
TotalSale LY YTD =
CALCULATE (
[Totalsale],
CALCULATETABLE (
SAMEPERIODLASTYEAR ( DATESYTD ( 'CALENDAR'[Date] ) ),
'CALENDAR'[DatesWithSales] = TRUE
)
)
Does this work for you?
Regards,
Owen
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 |
---|---|
96 | |
93 | |
83 | |
70 | |
65 |
User | Count |
---|---|
118 | |
106 | |
93 | |
79 | |
72 |