Monthly Total and Year Running Total up Month selection on Slicer

[cpmb]

Any ideas on how to get a monthly sum (decided by a Slicer selection) and a yearly sum (to the selected month on Slicer) to display on a table visual?

I have a Fact Table and Date Table, linked by Date.

Fact:

Location

Item

Date

Quantity

Date:

Date

Month

Year

My slicer is coming from the Date Table.

I attempted using the following measures:

[Month Quantity] = CALCULATE(SUM(Fact[Quantity]),FILTER(Fact,Fact[Item]=Fact[Item]&&Fact[Location]=Fact[Location]))

[Yearly Quantity] = CALCULATE([Month Quantity],ALL(Date[Month])

**Note: I also tried using the ALL function to filter by Month in the Fact table (ie: [Yearly Quantity] = CALCULATE([Month Quantity],ALL(Fact[Month]))

These result in the same value being displayed in my Table visual. I want to display these measures by Location and Item, hence the Filter function used in the [Month Quantity] measure. Any insight would be appreciated. 

[Ashish_Mathur · Accepted solution]

Hi,

Select a Year and Month in the 2 slicers that you have created from the Date Table.  Write these measures

Quantity = SUM(Fact[Quantity])

YTD Quantity = CALCULATE([Quantity],DATESYTD('Date'[Date]))

Hope this helps.