Forum Discussion
Using filters in a table with measures
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.
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
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,
KellyDid I answer your question? Mark my reply as a solution!
2 Replies
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
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,
KellyDid I answer your question? Mark my reply as a solution!