MTD sales from today

[michaelu1]

This seems like a simple question but I can't figure out what I am missing.

How can I write a measure to display month to date sales from today?

The issue that I am having is that how can I add the filter context of Today to this measure?

I specifically do not want a slicer or any other 'outside' context affecting this measure. I need the today context to be within the measure itself.

My next step is getting last months sales, but I assume once I have the context worked out the following steps will fall into place.

[Anonymous]

Hi @michaelu1 ,

According to your description, here are my steps you can follow as a solution.

(1) This is my test data. 

(2) We can create a measure. 

MTD = 
var _date=EDATE(MAX('Table'[date]),-1)
RETURN CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[date]>=_date && 'Table'[date] <=MAX('Table'[date])))

today mtd = 
 var _date=EDATE(TODAY(),-1)
RETURN CALCULATE(SUM('Table'[sales]),FILTER(ALL('Table'),'Table'[date]>=_date && 'Table'[date] <=TODAY()))

(3) Then the result is as follows.

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.

Best Regards,

Neeko Tang

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

[michaelu1]

Hi @Anonymous ,

I think your solution would work just fine.

My question though is if time intelligence dax could be used instead of having to filter the date table.

I attached a very simple file showing exactly what I am trying to do.

https://drive.google.com/file/d/1W0cFp3hV1k1MejyGXdvRpZzNHhU6TcRF/view?usp=drive_link

[Anonymous · Accepted solution]

Hi @michaelu1 ,

You can create a measure.

Measure = CALCULATE(CALCULATE(SUM(Sales[Sales]),DATESMTD('Date'[Date])),'Date'[Date]=DATE(2024,2,4))

Or:

Measure = CALCULATE(SUM(Sales[Sales]),DATESMTD(CALCULATETABLE(VALUES('Date'[Date]),'Date'[Date]=DATE(2024,2,4))))

If you need TODAY(), try this:

Measure 2 = CALCULATE(CALCULATE(SUM(Sales[Sales]),DATESMTD('Date'[Date])),'Date'[Date]=TODAY())

//CALCULATE(SUM(Sales[Sales]),DATESMTD(CALCULATETABLE(VALUES('Date'[Date]),'Date'[Date]=TODAY())))

Best Regards,

Neeko Tang

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

[michaelu1]

@Anonymous 

This is exactly it!

Thank you!

[Ritaf1983]

Hi @michaelu1 
Take a look at the linked discussion:
https://community.fabric.microsoft.com/t5/Desktop/MTD-TO-SHOW-CURRENT-MONTH-ONLY-IGNORING-ALL-FILTER...

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

[michaelu1]

That will likely work, but I will lose the ability to use time intelligence.

Is there a way to write a simple calculate(sum(sales[amount]),datesmtd(date))  (please excuse the shorthand, I'm writing this on my phone) and apply a context of Today within the measure?

I know if I would write the above measure, and have a slicer on the page with today selected then it would work. Can I do something similar within a measure?

[Ritaf1983]

Yes but to show how, more details are needed.

Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-...
Please show the expected outcome based on the sample data you provided.

https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523