Need help with DAX for month, quarter and year measures

[Avivek]

I have a report which needs to show MTD, QTD and YTD data as shown below:

Now the new requirement is we need to have Month instead of MTD, Quarter instead of QTD and Year instead of YTD. Also we need to have month, qurter and year slicers along.

So if I select months in month slicer Month Unit, Month Sales and Month Mix % should only change, similarly if I select any qurter in the quarter slicer only Quarter Unit, Quarter Sales and Quarter Mix% should change and no changes should be seen in the Month and Year columns. I tried creating a measure also-

Month Sales = CALCULATE(Sales[Sales Amount],FILTER('Date','Date'[Month Number]))

but this measure doesn't seem to work giving the desired result.

Can anyone suggest any other way of doing this or if the measure needs to be changed.

[v-deddai1-msft]

Hi @Avivek ,

You need three date tables for month , quanter and year:

Dim_DateforMonth = FILTER( 
ADDCOLUMNS (
CALENDAR (DATE(2013,1,1), DATE(2015,12,31)),
"Year", YEAR ( [Date] ),
"Quarter-Year", "Q" & FORMAT ( [Date], "Q" )&"-"&YEAR ( [Date] ),
"Month-Year", FORMAT ( [Date], "mmm" ) & "-" & YEAR ( [Date] )
),
[Date]<>BLANK())

Dim_DateforQuanter = Dim_DateforMonth 

Dim_DateforYear = Dim_DateforMonth 

Create the following relationship between fact table and these three date tables:

Create the following measure for monthvalue , quantervalue and Yearvalue:

SalesAmountformonth = SUM(Sales[Sales])

SalesAmountQuarter = CALCULATE([SalesAmountformonth],CALCULATETABLE(VALUES(Dim_DateforMonth[Date]),USERELATIONSHIP(Dim_DateforMonth[Date],Dim_DateforQuanter[Date]),REMOVEFILTERS(Dim_DateforMonth[Month-Year])))

SalesAmountYear = CALCULATE([SalesAmountformonth],CALCULATETABLE(VALUES(Dim_DateforMonth[Date]),USERELATIONSHIP(Dim_DateforMonth[Date],Dim_DateforYear[Date]),REMOVEFILTERS(Dim_DateforMonth[Month-Year])))

Create three slicers from these three date tables. Use month-year column from Dim_Dateformonth in month slicer and so on.

And for more details, please refer to the sample pbix: https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EfjssjDjpEhEizO8Jc...

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Best Regards,

Dedmon Dai

[PaulDBrown · Accepted solution]

@Avivek 

In that case a simple SUM should suffice:

Sum of sales = SUM(Sales[Sales amount])

If the date table is set up correctly with a one-to-many relationship between the Date table [Date] and the Sales [Date], using a slicer from the Date table and selecting a specific month or quarter or year will return the sales for the selected period.

If you need the values to be grouped by the period, try:

Month sales = CALCULATE([Sum of Sales], 
                FILTER(ALL('Calendar Table'), 
                'Calendar Table'[Month] = SELECTEDVALUE('Calendar Table'[Month])))

Qarter sales = CALCULATE([Sum of Sales], 
                FILTER(ALL('Calendar Table'), 
                'Calendar Table'[Quarter] = SELECTEDVALUE('Calendar Table'[Quarter])))

Year sales = CALCULATE([Sum of Sales], 
                FILTER(ALL('Calendar Table'), 
                'Calendar Table'[Year] = SELECTEDVALUE('Calendar Table'[Year])))

[Avivek]

Thankyou @PaulDBrown , this seems to work.

[PaulDBrown]

@Avivek 

Do you have a date table in your model?

[Avivek]

Yes @PaulDBrown