SUM Based on Model Sold for MTD, YTD, LMTD, LYTD

[rhall_hillintl]

I am attempting to create what was pretty simple to do in a Pivot Table in Power BI.

I need to SUM the GP$ for each model sold based on MTD, YTD etc.. I am currently just trying to get MTD to work then I could be fine. I also need to count the models sold for each interval.

The problem is, when the GP$ is 0 for the timeframe it is putting in a number that is not in the date range specified by 'TOTALMTD' which I would assume would only show me the JUNE numbers. What is weird to me is the total at the bottom of the table is showing correctly.. My MTD measure :

MTD = TOTALMTD(SUM(RAW[GP$]),RAW[Contract Date])

I also created a DATE table and linked it to my 'RAW' table with the same results:

MTD = TOTALMTD(SUM(RAW[GP$]),DimDate[Date])

Here are my results in Power BI which are incorrect for the MTD, and here is a sample from a Pivot Table inside of Excel which is correct:

[trajs]

This solution is not working with Other KPIs. I want to see the Category wise MTD & LMTD. Pls suggest..

[Ashish_Mathur]

Hi,

Share the link from where i can download your PBI file.

[rhall_hillintl · Accepted solution]

I got this fixed. It seems I needed to filter the dates even when using TOTALMTD, so I instead am just using CALCULATE.

MTD GP$ = CALCULATE(
        SUM(RAW[GP$]),
        FILTER(DimDate, DimDate[MonthofYear] = MONTH(NOW())))

Last MTD GP$ = CALCULATE(
        SUM(RAW[GP$]),
        FILTER(DimDate, DimDate[MonthofYear] = MONTH(NOW()) - 1))

[rhall_hillintl]

Thanks for the reply Ashish.

Sales.pibx