MTD sum does not give total till date

[Anonymous]

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

[v-angzheng-msft]

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.

[Anonymous]

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?

[Anonymous]

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?

[tamerj1]

@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!

[tamerj1]

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?

[Anonymous]

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.

[tamerj1]

Hi

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. 

[Anonymous]

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])

[tamerj1]

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?

[Anonymous]

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,

[ValtteriN]

Hi,

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

[Anonymous]

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.

[Anonymous]

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

[ValtteriN]

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/

[Anonymous]

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])

[tamerj1]

@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?

 

[Anonymous]

In this table the slicing is by area. 

[tamerj1]

@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.