Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!
Hello all,
I'm trying to calculate 'cumulative chargebacks' by lag in weeks, however, for those weeks where the chargebacks are 0 or blank, the cumulative chargebacks show a weird value. e.g. if you see for Lag (weeks) = 28, the cumulative chargebacks number is showing a weird number.
Im using the following measure for running total -
Solved! Go to Solution.
hello @rkaul ,
I created a measure using ALL instead of ALLSELECTED and the result seems fine
Cumulative Sum =
CALCULATE (
SUM ( Sheet2[Chargeback Amount] ),
FILTER (
ALL ( Sheet2[Lag (weeks)] ),
Sheet2[Lag (weeks)] <= MAX ( Sheet2[Lag (weeks)] )
)
)
Going back to your original post, what do you mean by sing an external date slicer? Are you using a separate dates table?
Anyway, here's the pbix based on your data: https://drive.google.com/file/d/1xc8F0qtTA_ydAMHetfyO0O58-6vyeVZG/view?usp=sharing
Proud to be a Super User!
hI @rkaul ,
Try changing your formula from ALLSELECTED to just ALL.
Cumulative Chargebacks Number =
CALCULATE (
[Number of Chargebacks],
FILTER (
ALL ( CB_report_Chargeback_data[Lag (weeks) (bins)] ),
CB_report_Chargeback_data[Lag (weeks) (bins)]
<= MAX ( CB_report_Chargeback_data[Lag (weeks) (bins)] )
)
)
Otherwise, please post a sample anonimized data.
Proud to be a Super User!
Here is the sample data
https://drive.google.com/file/d/1wMk6MORmKAb2HAPZPeM8diuHVyZJhEE9/view?usp=sharing
When I apply the transaction date filter, it creates an issue.
hello @rkaul ,
I created a measure using ALL instead of ALLSELECTED and the result seems fine
Cumulative Sum =
CALCULATE (
SUM ( Sheet2[Chargeback Amount] ),
FILTER (
ALL ( Sheet2[Lag (weeks)] ),
Sheet2[Lag (weeks)] <= MAX ( Sheet2[Lag (weeks)] )
)
)
Going back to your original post, what do you mean by sing an external date slicer? Are you using a separate dates table?
Anyway, here's the pbix based on your data: https://drive.google.com/file/d/1xc8F0qtTA_ydAMHetfyO0O58-6vyeVZG/view?usp=sharing
Proud to be a Super User!
I just deleted the original visual and redid it and it worked for some weird reason. I dont understand PBI many times, haha. Thanks a lot for your help 😄
Hi @rkaul ,
Haha. I am actually not sure what you are trying to achieve. I am a bit confused so I modified the pbix a bit and created two additional measures.
Proud to be a Super User!
I think you are getting the same error in your PBi also. if you look at weeks 21, 21, 23, 27 (lag) in the cumulative week column, its giving a weird value. What is your opinion on that?
which transaction dates?
Proud to be a Super User!
yes, i'm using an external date (calendar) table inplace of transaction date, you're right. From your PBI, it looks like transaction date works fine, but calendar date slicer (connected to the transaction date) doesn't seem to work. Is there a resolution to that? sorry i'm a bit new to PBI.
User | Count |
---|---|
131 | |
108 | |
101 | |
67 | |
64 |
User | Count |
---|---|
137 | |
115 | |
102 | |
71 | |
61 |