Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
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.
User | Count |
---|---|
57 | |
21 | |
19 | |
18 | |
16 |
User | Count |
---|---|
85 | |
80 | |
52 | |
37 | |
22 |