MTD Total end date not filtering

[Peter_au]

Hello,

I am using a date table to calculate a running MTD sum. The sum works, but does not filter the End date for the month as shown below. I got the formula on and is shown below.

Any helpful suggestions would be appreciated.

Thanks

[nandic]

Hi Peter,

I tested, if i select specific date interval in month, data is displayed inside that interval.

Screenshot below:

When i selected dates (bottom part of image), it filtered these days on list and on chart.

If still doesn't help, feel free to send pbix file and i will check.

Notes: there also might be "conflict" in your report if there are measures which for example have plans, but don't have actuals, these days will always be visible. Example: you filter actuals, but as there are planned values for specific dates, these days are visible.

Cheers,
Nemanja

[Peter_au]

Hi Nandic,

How did you construct your month filter?

Do you mind if I send an empty pbix file, without data?

[nandic]

Month filter comes from Date dimension and it is in format yyyymm (202001 is January 2020).
List and chart use Date field from Date dimension and created measures.
Measure Cumulative sales: 

Cumulative sales = CALCULATE(SUM(Sales[Sales]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])))
Sales = field from Sales table.

Date dimension:


Sales fact:


Here is relationship between these tables:


If still not working, you can send screenshots.

Nemanja

[Peter_au]

Hi Nandic,

I have a date table, but it was suggested to use an independant dat table to produce these tables. Should I use my date tables that currently have the proper relationships, as you suggested?

[nandic]

Could you try this approach?

Dax measure: 

Cumulative sales = CALCULATE(SUM(Sales[Sales]),FILTER(ALLSELECTED('Date'),'Date'[Date]<=MAX('Date'[Date])))

If you limit filter to only only one month, it will show you running total for selected month, if you allow multiple select, it will display cumulative amount for whole period.
Screenshot with examples for daily and monthy layout.

[Peter_au]

Hello Nandic,
That is in fact very close to what I would like.
The difference would be that I wlould like the user to pick the date that they want and the graph would show the data from the beginning of the month to that date.

Below is an example of what I am trying to do. You will notice that the data goes beyond the date in the graph.

dateplanplan_crealreal_c

2020-01-01	15	15	15	15
2020-01-02	15	30	20	35
2020-01-03	15	45	36	71
2020-01-04	15	60	69	140
2020-01-05	15	75	8	148
2020-01-06	15	90	52	200
2020-01-07	15	105	15	215
2020-01-08	15	120	12	227
2020-01-09	15	135	20	247
2020-01-10	15	150	14	261
2020-01-11	15	165	5	266
2020-01-12	15	180	3	269
2020-01-13	15	195	6	275
2020-01-14	15	210	15	290
2020-01-15	15	225	85	375
2020-01-16	15	240	55	430
2020-01-17	15	255	23	453
2020-01-18	15	270	20	473
2020-01-19	15	285	12	485
2020-01-20	15	300	15	500
2020-01-21	15	315	16	516
2020-01-22	15	330	18	534
2020-01-23	15	345	22	556
2020-01-24	15	360
2020-01-25	15	375
2020-01-26	15	390
2020-01-27	15	405
2020-01-28	15	420
2020-01-29	15	435
2020-01-30	15	450
2020-01-31	15	465

This almost works, but the dates keep going after the end of the month and doesn't work properly with the graph.

MTD_Planned_tonnes_2 = calculate([Daily_Tonnes_Plan],filter(ALL(tbl_Tonnes_Tenneur_Plannifier_Jour),

tbl_Tonnes_Tenneur_Plannifier_Jour[Date_Index] >= [SOM]

&& tbl_Tonnes_Tenneur_Plannifier_Jour[Date_Index] <= [EOM]

&& tbl_Tonnes_Tenneur_Plannifier_Jour[Date_Index]<=max(tbl_Tonnes_Tenneur_Plannifier_Jour[Date_Index])

)

)

[v-jayw-msft]

Hi @Peter_au ,

What's your expected out put? Show MTD from selected date to the end of month?

If so, you can create a mseasure like

IF(selectedvalue(date_index)>[eom],1,0)

Then add this measure to visual filter measure = 1.

Best Regards,

Jay

Community Support Team _ Jay Wang

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

[amitchandak]

@Peter_au , For such cases, prefer to use time intelligence with a date calendar

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.

[Peter_au]

The Data table is connected with a many to one relationship to a calander table marked as a date table.

What I am trying to do is have a chart showing the cummulative mtd total for the planning,(showing the full month), comared to the running total MTD of the current data, depending on the day.

[nandic]

Did you try using TOTALMTD function? It should work for month to date results.
Example: 

MTD measure = TOTALMTD([measure],'Date'[Date])

Another point of view, you should check data and relationships, it seems dates column is not consistent. Compare date column from Date table and date_index column to see if there are multiple rows per unique date.

[Peter_au]

Hello Nandic, I have a one to many relationship between a date table and the data table, to give consistency.

This is what Totalmtd does