Forum Discussion

doubi1899's avatar
doubi1899
Regular Visitor
2 years ago
Solved

YOY calculation

For first column I use following code to calculate the total revenue of crruent month. It did work.

"Current = TOTALMTD([total revenue],'IOSalesRecordbyCustomer'[Date])"

 

And second column I want to calculate the total revenue of same month of last year.  By the way , I already create a datesheet by using "Dates = CALENDAR("2022/01/01", "2024/06/01")"

Following is the code of the YOY

 

"YOY = CALCULATE(sum(IOSalesRecordbyCustomer[Total Revenue]),PARALLELPERIOD('Dates'[Date],-1,MONTH))"

 

It failed and not sure why shows the same number ( the exact number - the total revenue of current month)

https://drive.google.com/file/d/10wx09SN3fd5oceYe4PvKxadHZCsITUw4/view?usp=drive_link  

 
 
Need Help
  • BeaBF's avatar
    BeaBF
    2 years ago

    doubi1899 I think I've identified the problem.
    1. Relate the Dates table to the Fact table via a many-to-one relationship, like this:

     

    2. Then Use Date field of Dates Table in the matrix, not the field of the fact table.

     

    Your measure will work.

     

     

     

    BBF

4 Replies

  • BeaBF's avatar
    BeaBF
    Super User
    2 years ago

    doubi1899 Hi! I'va sent you the request to access the file. 

    Meanwhile, try with these formulas:

    Current = TOTALMTD([Total Revenue], 'Dates'[Date])

    YoY = CALCULATE(
    SUM(IOSalesRecordbyCustomer[Total Revenue]),
    SAMEPERIODLASTYEAR('Dates'[Date])
    )

    YoYComparison = [CurrentMonthRevenue] - [YoY]

     

    BBF

      • BeaBF's avatar
        BeaBF
        Super User
        2 years ago

        doubi1899 I think I've identified the problem.
        1. Relate the Dates table to the Fact table via a many-to-one relationship, like this:

         

        2. Then Use Date field of Dates Table in the matrix, not the field of the fact table.

         

        Your measure will work.

         

         

         

        BBF