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.

     

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

Hi Lee,

 

Help me with this please... 

 

Rgds, Cristina

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