Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hello - I believe I need to be using the ISBLANK and FIRSTNONBLANK to solve my issue...just not quite sure how to incorporate into my current measure.
My measure below basically works...except that if there is no value, it shows a blank. I beleive this is connected to the "actual value" field. And the last value that would be here (in our accounting system) would have been about 4 days ago (we manufacture things and due to the virus have not shipped anything in four days)...so I need to find the last "nonblank" value...and that should then give me March MTD.
The data is based on accounting ledger data that is reconciled at midnight...so the data is always one day behind. For example, January total is calculated as of Feb 1. Feb total is calculated as of March 1. etc.
Any help is appreciated!
Solved! Go to Solution.
@Anonymous
Try:
Measure 7 =
CALCULATE(SUM(Flu_Snapshots[Actual Value]),FILTER(ALL('Dates'),MINX(FILTER('Dates',EARLIER([Date])+1=[Date]),Flu_Snapshots[Actual Value]))))
Best regards
Paul Zheng
@Anonymous
Try:
Measure 7 =
CALCULATE(SUM(Flu_Snapshots[Actual Value]),FILTER(ALL('Dates'),MINX(FILTER('Dates',EARLIER([Date])+1=[Date]),Flu_Snapshots[Actual Value]))))
Best regards
Paul Zheng
@Anonymous ,Try if one of this can work
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Dates[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Dates[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = lastdate(Flu_Snapshots[Date] )))
CALCULATE(SUM(Flu_Snapshots[Actual Value]), FILTER(ALL(Dates[Date]), Dates[Date] = max(Flu_Snapshots[Date] )))
Actually this measure works to give me the March MTD
You mean like this? I tried it and got an "the end of the input was reached" error message.
User | Count |
---|---|
99 | |
87 | |
81 | |
76 | |
71 |
User | Count |
---|---|
112 | |
105 | |
96 | |
74 | |
66 |