Forum Discussion

leebm's avatar
leebm
Regular Visitor
10 years ago
Solved

Compare MTD with previous period

  Hi, I want to compare actual sales (running value) with sales in the same month last year. This should be shown in a Table and grouped by sales representative. I have a fact table with the sales ...
Need Help
  • leebm's avatar
    leebm
    10 years ago

    I finally solved it: I filter my measure with the datesbetween function. I created a measure for the beginning of the month last year (eomonth function) and another for the actual date (=EDATE(TODAY);-12)

    This way I have always the actual month compared to the month last year, even if the month is not yet finished.

     

kcantor's avatar
kcantor
Icon for Community Champion rankCommunity Champion
10 years ago

Everything works fine until we get to months that have only partial results due to being within that period.  Generally, my reports focus down to the week number in which case I have a field I added that shows the year and week together such as 2016-08 for the eight week of 2016. This allows me to use full weeks across multiple years within a report by adding the year-week to the axis or row instead of week number which would calculate week 8 for every year selected in my data and sum it up. If, however, the PTB want up to the current day, I have the option to filter the report by current year and day of the year using calculations built on total sales, LY total sales, and 2 year prior total sales up to the current day of the year. While this doesn't happen often, the request has been made in the past.

Then again, on second thought, if you are only interested in looking at data to the level of week over week within the given month, the year-week calculation may work for you.

As I said, day of the year is a workaround. Not a solution. I generally keep my date table down to the current day only which helps.

 

FYI: The PTB are the powers that be. They always ask for odd calculations or weird time frames. I think they just want to know if I can work that out.

leebm's avatar
leebm
Regular Visitor
10 years ago

I finally solved it: I filter my measure with the datesbetween function. I created a measure for the beginning of the month last year (eomonth function) and another for the actual date (=EDATE(TODAY);-12)

This way I have always the actual month compared to the month last year, even if the month is not yet finished.

 

  • themistoklis's avatar
    themistoklis
    Icon for Community Champion rankCommunity Champion
    8 years ago

    Sachet_716

    ako

    jbjs5820

     

    This is the formula that you can use:

    Turnover MTD (PY) = CALCULATE(SUM('Invoices to Clients'[TurnOver]),
        DATESBETWEEN('Calendar'[Date],                    
            FIRSTDATE(DATEADD('Calendar'[Date],-12,MONTH)),   
            IF(MONTH(MIN('Calendar'[Date])) = MONTH(TODAY()), EDATE(TODAY(),-12) , LASTDATE(DATEADD('Calendar'[Date],-12,MONTH))) 
         )
     )

    I have put the IF statement because i want for the months prior to current month to get all the dates

  • ako's avatar
    ako
    Regular Visitor
    9 years ago

    Hi leebm!

     

    I have the exact same problem. Could you please explain the solution more presis for me?  (I'm a slow learner :)

  • jbjs5820's avatar
    jbjs5820
    Icon for Helper I rankHelper I
    9 years ago

    Could you insert the total formula?

     

  • jbjs5820's avatar
    jbjs5820
    Icon for Helper I rankHelper I
    9 years ago

    Can you insert the total formulas?

    Thanks and regards

    JS

  • Sachet_716's avatar
    Sachet_716
    Icon for Helper I rankHelper I
    8 years ago

    Hi!  

     

    Were you able to figure this out? Can you share the measure please? Been searching for this function and it's a show stopper for me, can't go ahead without completing this. thank you in advance! :)

  • Sachet_716's avatar
    Sachet_716
    Icon for Helper I rankHelper I
    8 years ago

    Hi Lee,

     

    Help me with this please... 

     

    Rgds, Cristina

  • vinaybangari's avatar
    vinaybangari
    Frequent Visitor
    7 years ago

    Hi Leebm,

     

    Can you please post your complete DAX expression for previous YTD, MTD & QTD periods

  • vinaybangari's avatar
    vinaybangari
    Frequent Visitor
    7 years ago

    This is Previous YTD works fine: Prev YTD = CALCULATE([Current YTD],SAMEPERIODLASTYEAR('Calendar'[Date]))

     

    Prev MTD Period Balance = CALCULATE([Period Balance],DATEADD('Calendar'[Date],-1,MONTH))

  • Prateek03's avatar
    Prateek03
    Regular Visitor
    7 years ago

    Hi leebm i'm facing the same issue, can you please share the DAX of how you solved this.

     

    Thanks

  • Anonymous's avatar
    Anonymous
    Not applicable
    6 years ago

    Hi leebm ,

     

    Could you be able to show a pbxi file?

    I am looking for similar solution, however does not get it yet.

     

    Hope it is Ok to create a new post , and invite you for a answer. 😉

    Best regards,

    Yuqi