March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early bird discount ends December 31.
Register NowBe one of the first to start using Fabric Databases. View on-demand sessions with database experts and the Microsoft product team to learn just how easy it is to get started. Watch now
I am trying to do a cumulative measure to add to a chart, however becuase my data structure consist of stacked monthly reports if I do so using the below code I end up with summing over repeated instances as each ID appears more than once, therefore I need to filter on the most recent report date - how would I adapt the code below to reflect this?
Cumulative Quantity := CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= MAX ( 'Date'[Date] ) ) )
Solved! Go to Solution.
Ohh now I understand! Try this! 🙂
Cumulative Quantity := VAR _date = SELECTEDVALUE('Table'[Date] )
VAR _period = SELECTEDVALUE('Table'[Report Date]) Return CALCULATE ( SUM ( Transactions[Quantity] ), ALL ( 'Table' ), 'Table'[Date] <= _date,
'Table'[Report Date] = _period
)
Try this:
Cumulative Quantity := VAR Mdate = MAX ( 'Date'[Date] ) Return CALCULATE ( SUM ( Transactions[Quantity] ), FILTER ( ALL ( 'Date'[Date] ), 'Date'[Date] <= Mdate ) )
Br,
Johannes
Thanks @tex628
I'm not sure this will work as the stack reports have the same date line duplicated in each - I have a column [report date] that I want to filter to be the max. I have included some sample data below, where the aim is to get the cumulative for each month for the April Report.
Report Date | Date | Value |
Jan-19 | 01/01/2019 | 500 |
Jan-19 | 01/02/2019 | 900 |
Jan-19 | 01/03/2019 | 800 |
Jan-19 | 01/04/2019 | 200 |
Feb-19 | 01/01/2019 | 500 |
Feb-19 | 01/02/2019 | 900 |
Feb-19 | 01/03/2019 | 200 |
Feb-19 | 01/04/2019 | 200 |
Mar-19 | 01/01/2019 | 500 |
Mar-19 | 01/02/2019 | 900 |
Mar-19 | 01/03/2019 | 200 |
Mar-19 | 01/04/2019 | 400 |
Apr-19 | 01/01/2019 | 500 |
Apr-19 | 01/02/2019 | 900 |
Apr-19 | 01/03/2019 | 200 |
Apr-19 | 01/04/2019 | 200 |
Can you convert the report date column into a proper date column and use that instead?
Jan-19 to 01/01/19
Feb-19 to 01/02/19
Etc...
Yes it is in the date format already.
Relationship is between 'Table'[Date] and 'Date'[Date] and has to stay that way?
I'm not sure I follow? I don't think the relationship matters.
Below is an example of where I hope to get to using the above data, the cumulative for the latest report (Apr-19), as a measure not column.
Report Date | Date | Value | Cumulative |
Apr-19 | 01/01/2019 | 500 | 500 |
Apr-19 | 01/02/2019 | 900 | 1400 |
Apr-19 | 01/03/2019 | 200 | 1600 |
Apr-19 | 01/04/2019 | 200 | 1800 |
Ohh now I understand! Try this! 🙂
Cumulative Quantity := VAR _date = SELECTEDVALUE('Table'[Date] )
VAR _period = SELECTEDVALUE('Table'[Report Date]) Return CALCULATE ( SUM ( Transactions[Quantity] ), ALL ( 'Table' ), 'Table'[Date] <= _date,
'Table'[Report Date] = _period
)
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
User | Count |
---|---|
93 | |
90 | |
86 | |
76 | |
49 |
User | Count |
---|---|
167 | |
149 | |
99 | |
73 | |
57 |