Mapping of two different Dates for running total calculation

[Anonymous]

Hi 

I am trying to calculate cumulative update  of target table and  plot the bar line chart using cumulative target and cumulative amount column which should get filtered by amount date.I tried creating Date table as one to filter both the table bot it did not work.

But it is just giving values for 5 months.

below are data table

Any suggestion would be appreciated.

[v-alq-msft]

Hi, @Anonymous 

Based on your description, I created data to reproduce your scenario. The pbix file is attached in the end.

Amount:

Target:

Calendar(a calculated table):

Calendar = CALENDARAUTO()

Relationship:

You may create two measures as below.

Cumulative Amount = 
CALCULATE(
    SUM(Amount[Amount]),
    FILTER(
        ALL(Amount),
        [Amount Date]<=MAX('Calendar'[Date])
    )
)

Cumulative Target = 
CALCULATE(
    SUM(Target[Target]),
    FILTER(
        ALL(Target),
        [Target Date]<=MAX('Calendar'[Date])
    )
)

Finally you need to use 'Date' column from 'Calendar' table as x axis.

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft :

The  given formulae  are working now for Date column and filtering properly ,but now issue is the cumulative total does not get filtered  by Category  column.

Any suggestion would be appreciated.

[v-alq-msft]

Hi, @Anonymous 

You may modify the relationship and formula as below.

Relationship:

Formula:

Cumulative Amount = 
CALCULATE(
    SUM(Amount[Amount]),
    FILTER(
        ALLSELECTED(Amount),
        [Amount Date]<=MAX('Calendar'[Date])
    )
)

Cumulative Target = 
CALCULATE(
    SUM(Target[Target]),
    FILTER(
        ALLSELECTED(Target),
        [Target Date]<=MAX('Calendar'[Date])
    )
)

Result:

Best Regards

Allan

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Anonymous]

Hi @v-alq-msft 

Thank you for your inputs.But it is still not working.

I am attaching the PBIX file.

https://1drv.ms/u/s!AjdwZYc0AFcWhxe_JX9O9F_rHR-h?e=GBv57x 

Thanks in advance

[v-alq-msft]

Hi, @Anonymous

It seems correctly with your formulas. Could you please show us the expected result?

Best Regards

Allan

[Anonymous]

Hi @v-alq-msft 

Cumulative Target count does not change if I use the field  "Asset" from Document table as a slicer .

case 1:Cumulative target changing when Asset field used from same table

case 2:Using asset filed from Document table to filter cumulative target and culmulative document count.

So If I use Target asset field it only filters cumulative target not cumulative document count similarly if I use Document asset field it only filters cumulative document count not cumulative target measure.

I want one common slicer which can filter both cumulative measures.

[parry2k]

@Anonymous if I understood correctly, you added a date table and created the relation from the date table with these two tables on the date, 'one to many', one on the date table side, and many on these table sides, correct? Until this part all is correct, share your cumulative total measure to look into it why it is not working.

[Anonymous]

Hi @parry2k ,

Yes ,that's correct ,"Date table" is connected to both the tables using one to many relationship.

Please find measure formula ,for target cumultaive

Target Count running total in Date =
CALCULATE(
SUM('Target table'[target]),
FILTER(
ALL('Date Table'[Date Column]),
'Date Table'[Date Column]<=MAX('Date Table'[Date Column])
)
)