Get total of multiple months and show per month

[Spekko]

Hi everyone,

I've got the following data:

Datekey	Value
20200101	1000
20200201	1000
20200301	1500
20200401	1500
20200501	750
20200601	1000

The timestamp is linked to a separate date table.

Based on that I currently have a chart with a running total (1000, 2000, 3500, 5000, ...) and a monthly value (exactly what is shown in the table above).

However, I would like to show the sum of the values for every month, based on the time-filter i'm using on the dashboard.

So let's say i'm filtering on Q1 2020. then in the chart I would want to see:

jan-2020 - 3500

feb-2020 - 3500

mar-2020 - 3500

How can I achieve that?

[amitchandak]

@Spekko , create a date table move your qtr, month, year filter there. and then try a measure like example with month (from date table) and check

example

Cumm Sales = CALCULATE(SUM(Sales[Sales Amount]),filter(allselected(date),date[date] <=max(date[date])))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.

[Fowmy · Accepted solution]

@Spekko 

Your measure will have to be like:


Measure = Calculate ( sum(table[sales]) , allselected(Date[MonthYear])

________________________

If my answer was helpful, please consider Accept it as the solution to help the other members find it

Click on the Thumbs-Up icon if you like this reply 🙂

YouTube  LinkedIn