Month to date, ignoring context measure formula help!

[djk1000]

Hi,

I'm trying to figure out a DAX measure formula and I've hit a wall, I'd appreciate some help!

I want to show the cumulative month to date sales, regardless of the current context. I have a slicer with different periods like "Current Week", "Current Month" etc. If I select "Current week" I have a cumulative sum which shows the cumulative totals for the week, I can drill down to day and see that, no problem.

I would like to have a month to date measure that takes the first and last dates of the context month, and gives me a cumulative sum, but the first row of the context has the total for the month up to that point.

Example:

If the Period is for instance "Financial Year" I would like this to work for each displayed month in the year, but historic months would of course show the same as the cumulative.

I have a date dimension and if it helps, I have FirstDateofMonth and LastDateOfMonth columns. 

Thanks for your help!

[v-sihou-msft]

@djk1000

In this scenario, since you already have date column in your table, you can directly use TOTALMTD to get Month to date total. It will calculate up to current row date, no matter what you selected in slicer.

MTD = TOTALMTD(SUM('Table'[Amount]),'Table'[Date]) 

Regards,

[djk1000 · Accepted solution]

 I got it in the end:

Measure MTD = CALCULATE ([Sales], 
			  FILTER(ALL('Date Dynamic Period Dimension'),'Date Dynamic Period Dimension'[Date]>= MIN( 'Date Standard Dimension'[StartOfMonthDate])),
              FILTER (ALL ( 'Date Standard Dimension' ), 'Date Standard Dimension'[Date] >= MIN( 'Date Standard Dimension'[StartOfMonthDate] ) && 'Date Standard Dimension'[Date] <= MAX ('Date Standard Dimension'[Date])
    )
)

If you follow the tutorial I mentioned above, you get a standard date dimension and a dynamic date dimension (M2M join between them), my slicer is using the dynamic dimension. So I calculated the MTD with filters to reference both date tables.  I used the StartOfMonth and EndOfMonth columns in the standard date dimension, but I could do that in DAX too.

[djk1000]

Hi,

No answer to this yet, I wonder if the question makes sense? This is driving me crazy so any help would be greatly appreciated! In my post I mentioned a date period slicer, to achieve this I followed this tutorial:

https://www.fourmoo.com/2016/09/07/create-dynamic-periods-for-fiscal-or-calendar-dates-in-power-bi/

Thanks

!D