Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
rhall_hillintl
Regular Visitor

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

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:

Capture.JPGCapture.JPG

1 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))

View solution in original post

4 REPLIES 4
trajs
New Member

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

Ashish_Mathur
Super User
Super User

Hi,

 

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


Regards,
Ashish Mathur
http://www.ashishmathur.com
https://www.linkedin.com/in/excelenthusiasts/

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))

Thanks for the reply Ashish.

 

Sales.pibx

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

Power BI Carousel June 2024

Power BI Monthly Update - June 2024

Check out the June 2024 Power BI update to learn about new features.

PBI_Carousel_NL_June

Fabric Community Update - June 2024

Get the latest Fabric updates from Build 2024, key Skills Challenge voucher deadlines, top blogs, forum posts, and product ideas.

Top Solution Authors