topic Re: MTD sum does not give total till date in DAX Commands and Tips

MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 07:38:49 GMT

I have sales and target colums in sales report.

i wanted to make target month to date.

for sales i used the simple formula:

MTD Sales= TOTALMTD([NetTotal] ,SalesmanTarget[TargetDay]) NetTotal is the sum of sales & TargetDay is the date column

the result is correct

I made another formula

MTD Target= TOTALMTD([Actual Target],SalesmanTarget[TargetDay])

where

Actual Target= SUM(SalesmanTarget[TargetAmount])

and the results are as follows MTD sales and Actual Target are correct, but the MTD target is not. MTD Target should be the target assigned to the sales team from start of the current month till date. but as you see i am not reaching the correct sum for MTD Target.

is there a mistake in my formulas?

I appreciate your help and support

Re: MTD sum does not give total till date

ValtteriN — Wed, 23 Feb 2022 07:37:09 GMT

Hi,

I would try uisng a calendar table for date column reference. Since DAX is filter context dependand I suspect now your measure is calculating day level sales instead of MTD values.

I hope this post helps to solve your issue and if it does consider accepting it as a solution and giving the post a thumbs up!

My LinkedIn: https://www.linkedin.com/in/n%C3%A4ttiahov-00001/

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 07:46:43 GMT

Thank you for your reply.

I have the date table ready. so now i tried with the date table but the results did not change.

as you can see below, the MTD Target and Actual Target are still equal. The formulas are as follows:

MTD Sales = TOTALMTD([NetTotal] ,DatesTable[Date])

MTD Target = TOTALMTD([Actual Target] ,DatesTable[Date])

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 12:44:56 GMT

UPDATE I also tried the following formula,

MTD Target = CALCULATE(SUM([Actual Target]), DATESMTD(SalesmanTarget[TargetDay]))
MTD Target= CALCULATE(SUM([Actual Target]), DATESMTD(DatesTable[Date]))

Issue is not resolved yet

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 13:13:35 GMT

UPDATE

I tried the builtin measure and the same issue still not resolved.

TargetAmount MTD =

IF(

ISFILTERED(SalesmanTarget[TargetDay]),

TOTALMTD(SUM('SalesmanTarget'[TargetAmount]), SalesmanTarget[TargetDay])

)
the result is blank,

Re: MTD sum does not give total till date

ValtteriN — Wed, 23 Feb 2022 13:33:40 GMT

Hi,

How do you calculate [NetTotal]? This might be the root problem.

Re: MTD sum does not give total till date

tamerj1 — Wed, 23 Feb 2022 13:50:48 GMT

Hi Anonymous ,
Can you please share a acreenshot of your data model showing the realtionships and connected culumns? Also can you confirm what are slicing by? And from which table?

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 14:49:54 GMT

thank you for your reply.

Additional step i took today. I removed PowerBI and installed the latest version, restarted my machine. I thought maybe some calculations stuck in memory. 😁 Just for sake of me doing whatever it takes.

Re: MTD sum does not give total till date

tamerj1 — Wed, 23 Feb 2022 15:01:37 GMT

you should be using dates only from date table. Slice by month from the date table only. Time intelligent functions works only with a standard date tables that is marked as date table.

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 16:53:44 GMT

I appreciate taking the time

as you notice in the following formula, I am using the DatesTable

MTD Target = TOTALMTD([Actual Target] ,DatesTable[Date])

if this is what you mentioned in your reply.

also the below not working, its giving blank:

TargetMTD = TOTALMTD(SUM(SalesmanTarget[TargetAmount]) ,DatesTable[Date])

Re: MTD sum does not give total till date

tamerj1 — Wed, 23 Feb 2022 17:13:14 GMT

Yes I saw that already but in your report you are slicing by a column that not visible in the screenshot. Is that the month nsme column from the date table?

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 17:48:39 GMT

I am using [NetTotal] in calculating sales only. nettotal is sum of sales coming from TransactionEntry table. theres no complicated calculation in NetTotal.

Target is a bigger issue though. i am trying to sum the target, but i dont need the whole moth target, i need only the Sum of target till date or MTD.

Re: MTD sum does not give total till date

tamerj1 — Wed, 23 Feb 2022 18:27:20 GMT

Anonymous

in your report you are slicing by a column that is not visible in the screenshot. Is that the "month name" column from the date table? If not what is it?

Re: MTD sum does not give total till date

Anonymous — Wed, 23 Feb 2022 21:21:26 GMT

In this table the slicing is by area.

Re: MTD sum does not give total till date

tamerj1 — Thu, 24 Feb 2022 02:41:26 GMT

Anonymous

Either you select a date on a slicer. or in the table you slice by date/date hierarchy and keep the area on the slicer (or on the columns). When say "month to date" the DAX engine needs to understand which date. Once you tell the engine which date then you'll start to see differences between you different measures.

Re: MTD sum does not give total till date

Anonymous — Thu, 24 Feb 2022 08:39:50 GMT

UPDATE

When I try to creat MTD by Quick Measure, it gives me the following error:

Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column

Does that mean anything in context of the issue?

Re: MTD sum does not give total till date

tamerj1 — Thu, 24 Feb 2022 08:43:55 GMT

Anonymous

Please refer to my reply below. You should be slicing by date hierarchy not area. MTD has no meaning if you slice by area!

Re: MTD sum does not give total till date

Anonymous — Thu, 24 Feb 2022 09:18:38 GMT

UPDATE

When I try to creat MTD by Quick Measure, it gives me the following error:

Time intelligence quick measures can only be grouped or filtered by the Power BI-provided date hierarchy or primary date column

Does that mean anything in context of the issue?

Re: MTD sum does not give total till date

v-angzheng-msft — Tue, 01 Mar 2022 05:37:20 GMT

Hi, Anonymous

Not sure what your context is like, but you can try my formula:

MTD = CALCULATE(SUM(Table2[Sale]),FILTER(ALL(Table2),EOMONTH('Table2'[Date],0)=EOMONTH(MAX('Table2'[Date]),0)&&'Table2'[Date]<=MAX('Table2'[Date])))

I use this formula a lot and it always works for me, and this doesn't need to rely on a date table.

Result:

Best Regards,
Community Support Team _ Zeon Zheng

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