Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago

Months comparison based on data dates

Hello,

 

I need Month vs Month comparison based on dates in my table (not based on "today()"). Table is auto updated twice a month so what I need is to compare latest month in data with previous month e.g. if today is 2019/05/23 and the latest date aviable in data is 2019/04/12, then compare april with march.

 

MvM% = 

VAR CYear = YEAR(LASTDATE(SOURCE[DATE]))
VAR CMonth = MONTH(LASTDATE(SOURCE[DATE]))
VAR LMonth = PREVIOUSMONTH(LASTDATE(SOURCE[DATE]))
VAR CCMonth = CALCULATE(COUNT(SOURCE[UCET]),FILTER(ALL(SOURCE),MONTH(SOURCE[DATE])=CMonth))
VAR CLMonth = CALCULATE(COUNT(SOURCE[UCET]),FILTER(SOURCE,IF(CMonth=1,LMonth=12 && YEAR(SOURCE[DATE])=CYear-1,MONTH(SOURCE[DATE]) = LMonth && YEAR(SOURCE[DATE])=CYear)))

return DIVIDE(CCMonth-CLMonth,CLMonth,0)

2 Replies

  • v-frfei-msft's avatar
    v-frfei-msft
    Community Support

    Hi Anonymous ,

     

    Could you please share your sample data and excepted result to me if you don't have any Confidential Information. Please upload your files to One Drive and share the link here.

    • Anonymous's avatar
      Anonymous
      Not applicable

      Hi,

       

      Unfortunately, I cannot share my data but you need just 2 columns: first with dates of every day from the start of the year and the second can be just random number because you just count them.