Forum Discussion

rkaul's avatar
rkaul
Frequent Visitor
6 years ago
Solved

Cumulative total issue for 0 count

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 ...
Need Help
  • danextian's avatar
    danextian
    6 years ago

    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 

danextian's avatar
danextian
Super User
6 years ago

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 

rkaul's avatar
rkaul
Frequent Visitor
6 years ago

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.