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

Get certified in Microsoft Fabric—for free! For a limited time, the Microsoft Fabric Community team will be offering free DP-600 exam vouchers. Prepare now

Reply
Anonymous
Not applicable

TOTALMTD custom dates

Hello,

 

I'm trying to calculate TOTALMTD for a field but our monthly 'dates' dont align perfectly with a natural calendar date, e.g. what we call "Month 2" rather than simply being February actually comprises all dates from 30/01/2020 to 26/02/2020, I have a dates table which links every natural date to what we call each month internally (CustomMonth).

 

Is there a way to use TOTALMTD but have it work with my field "CustomMonth" rather than the natural date field? Alternatively another way to get cumulative values from the start of the current "custommonth" until today()? But have it unaffected by slicers/filters on the visual in the same way TOTALMTD is unaffected

 

I tried to use the following cumulative totals formula which does work with my customMonth field, but filtering to say week 3 of the month shows a value just for that week rather than for the customMonth to week 3:

 

PTD Sales = Calculate(

sum(Table1[Sales]),

FILTER(

ALLSELECTED(Dates),Dates[Date] <= MAX(Dates[Date])

)

)

 

The above code is obviously affected by my CustomMonth selections on the visual, whereas I need them implicately limited to the current CustomMonth regardless of visual slices if that makes sense.

 

Many thanks

 

1 ACCEPTED SOLUTION
amitchandak
Super User
Super User

@Anonymous , Create a column like  // Assuming you have Month Start date

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1

 

and measures like

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here

View solution in original post

4 REPLIES 4
amitchandak
Super User
Super User

@Anonymous , Create a column like  // Assuming you have Month Start date

Month Rank = RANKX(all('Date'),'Date'[Month Start date],,ASC,Dense)
Month Day = DATEDIFF('Date'[Month Start Date],'Date'[Date],Day)+1

 

and measures like

This Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])))
Last Month = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1))
MTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank]) && [Month Day] <=max([Month Day])))
LMTD = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Month Rank]=max('Date'[Month Rank])-1 && [Month Day] <=max([Month Day])))

 

Join us as experts from around the world come together to shape the future of data and AI!
At the Microsoft Analytics Community Conference, global leaders and influential voices are stepping up to share their knowledge and help you master the latest in Microsoft Fabric, Copilot, and Purview.
️ November 12th-14th, 2024
 Online Event
Register Here
Anonymous
Not applicable

Hey Amit,

 

This worked for me, thanks! I can't quite follow what your code is doing though, in the interest of learning the why, is there a simplistic explanation for what those columns and measures are doing please?

mahoneypat
Microsoft Employee
Microsoft Employee

Please try this expression instead

 

PTD Sales =
VAR vThisCustomMonth =
    MIN ( Dates[CustomMonth] )
RETURN
    CALCULATE (
        SUM ( Table1[Sales] ),
        FILTER (
            ALLSELECTED ( Dates ),
            Dates[Date]
                <= MAX ( Dates[Date] )
        ),
        Dates[CustomDate] = vThisCustomMonth
    )

 

Or, if your visual includes your CustomMonth column, you can just use

 

PTD Sales = Calculate(

sum(Table1[Sales]),

FILTER(

ALLSELECTED(Dates[Date]),Dates[Date] <= MAX(Dates[Date])

)

)

 

Regards,

Pat





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

Hi Pat,

 

What is the CustomDate field you refer to here? my CustomMonth or something else?

 

thanks

Helpful resources

Announcements
OCT PBI Update Carousel

Power BI Monthly Update - October 2024

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

September Hackathon Carousel

Microsoft Fabric & AI Learning Hackathon

Learn from experts, get hands-on experience, and win awesome prizes.

October NL Carousel

Fabric Community Update - October 2024

Find out what's new and trending in the Fabric Community.