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 Team,
I have to calculate YTD using month name
Please refer the below screenshot for your referene.
Is it possible to do YTD without unpivot of these columns?I want to calculate YTD ,but since i have only month name im not sure how do i get that ?
Solved! Go to Solution.
@Anonymous
You may try the measure below.
Measure =
VAR m =
MAX ( 'Calendar'[Date].[MonthNo] )
RETURN
SUMX (
Table1,
Table1[Jan]
+ Table1[Feb]
* IF ( 2 <= m, 1, 0 )
+ Table1[Mar]
* IF ( 3 <= m, 1, 0 )
+ Table1[Apr]
* IF ( 4 <= m, 1, 0 )
+ Table1[May]
* IF ( 5 <= m, 1, 0 )
+ Table1[Jun]
* IF ( 6 <= m, 1, 0 )
+ Table1[Jul]
* IF ( 7 <= m, 1, 0 )
+ Table1[Aug]
* IF ( 8 <= m, 1, 0 )
+ Table1[Sep]
* IF ( 9 <= m, 1, 0 )
+ Table1[Oct]
* IF ( 10 <= m, 1, 0 )
+ Table1[Nov]
* IF ( 11 <= m, 1, 0 )
+ Table1[Dec]
* IF ( 12 <= m, 1, 0 )
)
My advice would be to unpivot and create a date like this
Date = "1-" & Table[Month] & "-2020" //If you do not have year
Date = "1-" & Table[Month] & "-" & @Table[Year] //If you do have year
Then you can use time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"12/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"12/31"))
Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"12/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"12/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"12/31"))
Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
Hi @amitchandak
Is there a possibility in power bi without unpivot to get the ytd.
In my report if i unpivot the number of rows are doubled because of this.
Please suggest.
Regards,
Husna
@Anonymous
You may try the measure below.
Measure =
VAR m =
MAX ( 'Calendar'[Date].[MonthNo] )
RETURN
SUMX (
Table1,
Table1[Jan]
+ Table1[Feb]
* IF ( 2 <= m, 1, 0 )
+ Table1[Mar]
* IF ( 3 <= m, 1, 0 )
+ Table1[Apr]
* IF ( 4 <= m, 1, 0 )
+ Table1[May]
* IF ( 5 <= m, 1, 0 )
+ Table1[Jun]
* IF ( 6 <= m, 1, 0 )
+ Table1[Jul]
* IF ( 7 <= m, 1, 0 )
+ Table1[Aug]
* IF ( 8 <= m, 1, 0 )
+ Table1[Sep]
* IF ( 9 <= m, 1, 0 )
+ Table1[Oct]
* IF ( 10 <= m, 1, 0 )
+ Table1[Nov]
* IF ( 11 <= m, 1, 0 )
+ Table1[Dec]
* IF ( 12 <= m, 1, 0 )
)
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 | |
95 | |
80 | |
68 | |
59 |
User | Count |
---|---|
150 | |
119 | |
104 | |
87 | |
67 |