Forum Discussion

snandy2011's avatar
snandy2011
Helper IV
8 years ago
Solved

DAX : Month To date formula help

 

Hi, Everyone,

 

I have created a moth to date measure by following formula

 

MTD = CALCULATE(SUM('Poker Profit'[Gross Gaming Revenue]), DATESMTD('Calendar'[Date])) 

 

It is showing me the correct numbers. 

 

Now, I am comparing this current month with others month by day wise. Over here I am facing a problem. When i click Other month on th slicer, It shows me those month's profit but, the MTD coulmn is also getting changed into corresponding month profit..I am attaching a screenshort for better understanding.Plase have a look,

 

 

Look at the scrrenshots, when i have clicked May on the slicer, Total Profit and MTD are both showing me same rasult. but, I want to fix this MTD coulmn.That means, whenever i will click any month on the slicer, the total profit column will be changed accordingly but, MTD column will show me this month value.

 

expected output : 

suppose i have selected may on the slicer

 

Day       MTD          Total profit

Sun       2500           4500

Mon      7500           5000

Tue       3000            4000

 

 

can you please suggest me how can i achieve that?

 

Any suggesation is really appreciable.

 

Thanks,

snandy2011

 

 

Need Help
  • Phil_Seamark's avatar
    Phil_Seamark
    8 years ago

    Hi @

     

    What happens when you use your 'Calendar'[Date] field on a visual with your calculated measure.  It seems strange to use it with a Week Day Name field.  MTD functions will try to cumulate your measure up over the days of the calendar month.

5 Replies

  • Phil_Seamark's avatar
    Phil_Seamark
    Microsoft Employee
    8 years ago

    Hi @

     

    What happens when you use your 'Calendar'[Date] field on a visual with your calculated measure.  It seems strange to use it with a Week Day Name field.  MTD functions will try to cumulate your measure up over the days of the calendar month.

    • snandy2011's avatar
      snandy2011
      Helper IV
      8 years ago

      Hi Phil_Seamark

       

      Thanks for your reply.. It is showing wrong result..

       

       

      Please have a look.. It should show only june's value..But it also shows may and other's month value..

      • Phil_Seamark's avatar
        Phil_Seamark
        Microsoft Employee
        8 years ago

        Hi snandy2011

         

        That's the way the MTD function works.  For whatever day on whatever month you pass to it, it will return what the cumulative value of the measure is to that day of that month.

         

        If you only want to show data for June, then use a slicer to filter on June so that only those days appear on the axis.  This is by design and is actually quite useful.