cancel
Showing results for
Did you mean:

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Anonymous
Not applicable

## Mapping of two different Dates for running total calculation

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.

8 REPLIES 8
Community Support

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
Not applicable

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.

Community Support

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
Not applicable

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

I am attaching the PBIX file.

Community Support

Hi, @Anonymous

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

Best Regards

Allan

Anonymous
Not applicable

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.

Super User

@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.

Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤

Did I answer your question? Mark my post as a solution. Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.

Anonymous
Not applicable

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])
)
)

Announcements

#### Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

#### Power BI Monthly Update - August 2024

Check out the August 2024 Power BI update to learn about new features.

#### Fabric Community Update - August 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors
Top Kudoed Authors