Using filters in a table with measures

[Shackwell]

Hello,

I have a database of a the last year and Im looking to create a measure that shows the cummulative cost from month to month. For example.

I require a measure that can sum with some sort of cummulative filters. Like having a filter to calculate the cost up until may 2020, or calculate all the costs up until aug 2020 or dec 2020.  I tried using a filter but was only showing individual months (for example was showing only the costs in september, and Im looking to see all the costs from january to september.

Thank you for your time.

[PhilipTreacy · Accepted solution]

Hi @Shackwell 

Download sample PBIX file with the following code and visuals.

SumCost = SUM('DataTable'[Cost])

You can use a slicer for the dates, set to 'Between', so you get a slider that you can select dates with.  The cost is then affected by the selected date range.

Regards

Phil

[v-kelly-msft · Accepted solution]

Hi @Shackwell ,

First create a calendar table as slicer;

Then create a measure as below:

Measure = 
var _dates=DATESYTD('calendar'[Date])
Return
IF(MAX('DataTable'[Date]) in _dates,SUM('DataTable'[Cost]),BLANK())

And you will see:

For the related .pbix file,pls see attached.

Best Regards,
Kelly

Did I answer your question? Mark my reply as a solution!

[PhilipTreacy · Accepted solution]

Hi @Shackwell 

Download sample PBIX file with the following code and visuals.

SumCost = SUM('DataTable'[Cost])

You can use a slicer for the dates, set to 'Between', so you get a slider that you can select dates with.  The cost is then affected by the selected date range.

Regards

Phil