Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
If I am given a target per quarter for a report.
For instance
Q1 = 6,
Q2 = 6,
Q3 = 6,
Q4 = 6
so basically for each calendar quarter I am given a standard 6 total of sale items.
How can i get the rolling total say I select February 5th. I am assuming those 6 will be divided by 90 days for me to get an accrued exact target from quarter start to feb 5th, approx. 2.4 items, or if i selected April 2nd, I should expect approx. 5.52. assuming I am drividing the target (6) by (90 days) of each quarter.
I want to be able to select a particular date and I get like the qtd rolling total of the target. I know I could assign variables for each quarter and then return the conditions to which I want them, but i dont think that works the way I want it. Please advise, thank you.
Solved! Go to Solution.
You could try multiplying your calculated daily target by COUNTROWS(DATESQTD('Date'[Date])).
And if you wanted to get the actual number of days in the chosen quarter, rather than assuming a flat 90, you could try something like
Days in quarter = CALCULATE( COUNTROWS('Date'), REMOVEILTERS('Date'),
'Date'[Year] = SELECTEDVALUE('Date'[Year]) && 'Date'[Quarter] = SELECTEDVALUE('Date'[Quarter]))
You could try multiplying your calculated daily target by COUNTROWS(DATESQTD('Date'[Date])).
And if you wanted to get the actual number of days in the chosen quarter, rather than assuming a flat 90, you could try something like
Days in quarter = CALCULATE( COUNTROWS('Date'), REMOVEILTERS('Date'),
'Date'[Year] = SELECTEDVALUE('Date'[Year]) && 'Date'[Quarter] = SELECTEDVALUE('Date'[Quarter]))
User | Count |
---|---|
53 | |
28 | |
19 | |
18 | |
14 |
User | Count |
---|---|
92 | |
86 | |
39 | |
24 | |
22 |