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.
I've data stored in my database as YTD values, and YTD data are not a sum of months.
I'm looking for a way to calculate quaters not as an aggregation of months but as a difference between YTD values of the last month of the quarter.
Fo example:
Expeted Result | |||||
Year | Quarter | Month | Sales YTD Amt | Sales Amt | |
2021 | January | 10 | 10 | ||
February | 23 | 13 | |||
March | 34 | 12 | |||
Q1 | 35 | 34 | |||
April | 47 | 11 | |||
May | 60 | 14 | |||
June | 73 | 13 | |||
Q2 | 38 | 39 |
Any ideas?
@amitchandak , I've some trouble implementing your solution. I'm using tabular with compatibility level 1400, and don't have isinscope function. I tried hasonevalue, but without success.
@ribaldo , You need a measure like this
if(isinscope(Date[Qtr]) && Not(isinscope(Date[Month])) , [sales YTD] - calculate([sales] -datesmtd( Date[Date])), blank())
Assuming you are using date table and time intelligence
YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD('Date'[Date],"12/31"))
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 :radacad sqlbi My Video Series Appreciate your Kudos.
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 |
---|---|
39 | |
25 | |
20 | |
14 | |
8 |
User | Count |
---|---|
71 | |
48 | |
46 | |
20 | |
16 |