The ultimate Fabric, Power BI, SQL, and AI community-led learning event. Save €200 with code FABCOMM.
Get registeredEnhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.
Hello,
I have a running total measure of costs with an end date and the quick measure function with some small changes gives a correct output.
I'd like to represent this in Quarters, however using the date hierarchy to do so runs into a problem as we don't have items expiring in every quarter, so when it hits an empty one it defaults to the max value as shown. This is a particular problem if I filter down into item type as there are many more empty quarters.
The desired output would be like this, giving the previous Quarter's value if there were no end dates in that Quarter.
Can the dax measure be changed to get this output? The relevant data columns I have are an identfying serial number, asset type, lease start date, lease end date and the quarterly lease cost.
Thanks.
edit: Formula should be SUM, not COUNTA. I've changed this but it does not affect any of the outputs.
Solved! Go to Solution.
Another thing I've tried was creating a table with all the years and quarters then doing a Full Outer Merge to give me the null Quarters then remake the measure, which is closer but now it's omitting the empty Quarters even if I select the "Show items with no Data" option in the visual".
Out of ideas.
Nevermind, used the wrong columns.
This is what I wanted.
@moldfield , Try a measure like the example
Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected('Date'),'Date'[date] >=max('Date'[date])))
Thank you for the response. The difference between this example (which I think is the default running total measure) and mine is the date column in the all selected and the max instead of min (since I need a decreasing total). Adding the column back in to the filter doesn't look right.
User | Count |
---|---|
27 | |
12 | |
8 | |
7 | |
5 |
User | Count |
---|---|
31 | |
15 | |
12 | |
7 | |
6 |