Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
8 years ago

Fiscal Calculation YOY% MOM% QOQ%

Hi ALL,

 

Im facing issue while creating YOY calculations like YOY% when i put on Card its not showing values.

 

My client fiscal year like nov-oct so he want us to use fiscal date instead calender date for calculating YoY MOM QOQ growth calculations.

 

Can any one tweak my dax query in my report and make it to work perfectly.

https://onedrive.live.com/?cid=53AAA0A594F215C2&id=53AAA0A594F215C2%21128&parId=root&o=OneUp 

 

 

Thanks in advance

kng

Need Help

6 Replies

  • v-piga-msft's avatar
    v-piga-msft
    Icon for Resident Rockstar rankResident Rockstar
    8 years ago

    Hi Anonymous,

     

    From your link, I cannot find anything you shared. Please check your link.

     

    If it is convenient, could you share  your sample again, so that we can help further investigate on it?

     

    If you could share your expected output that will be better.

     

    Best Regards,

    Cherry

      • v-piga-msft's avatar
        v-piga-msft
        Icon for Resident Rockstar rankResident Rockstar
        8 years ago

        Hi Anonymous,

         

        I have made a test with simple data to try to reproduce your sceanro, calculating the YOY%, MOM%and QOQ% .

         

        I found that the card visual shows blank is caused by lacking filter context. So we need select the filter option in Slicer to get the value. You also could find if you select the Year or Month in your Slicer, the card visual will show the value.

         

        For card visual, it calculate the global context by default.

         

        Best Regards,

        Cherry

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Icon for Super User rankSuper User
    8 years ago

    Hi,

     

    Here are some steps you can follow:

     

    1. In the Calendar Table, create this calculated column to compute the FY that each date falls into = IF(MONTH(Calendar[Date])<=10,Year(Calendar[Date])-1&"-"&Year(Calendar[Date]),Year(Calendar[Date])&"-"&Year(Calendar[Date])+1)
    2. In the Calendar Table, create a month column by using this formula = FORMAT(Calendar[Date],"mmmm")
    3. Create a Month order table (name it as Month_order) with all months listed in the first column and the order number in the second column.  So for you, November and December would be 1 and 2 and January to October would be 3 to 10
    4. Create a relationship from the Month column of the Calendar Table to the Month column of the Month_order table
    5. In the Calendar Table, write this calculated column formula to bring over the Order from the Month_order table = RELATED(Month_Order[Order])
    6. Click on any cell in the Month column of the Calendar Table and go to Sort by Colmnn > Order
    7. Drag the Year and Month from the Claendar Table to the filter/slicer
    8. Create these measures - [Total Sales] = SUM(Data[Sales]), [Total Sales last year] = CALCULATE([Total Sales],PREVIOUSYEAR(Calendar[Date]))

    Hope this helps.

    • Anonymous's avatar
      Anonymous
      Not applicable
      8 years ago

      Hi Ashish,

       

      Thanks for the reply but in my calendar table fy year quarter month is hard coded so I think you're trying to say so create manually!!

       

      Just need to get clarify on fiscal calculation to do we need to use calendar only or is their any option to pull fiscal year column in the calculation instead calendar one.

       

      Regards,

      kng

      • Ashish_Mathur's avatar
        Ashish_Mathur
        Icon for Super User rankSuper User
        8 years ago

        Hi,

         

        You may create a Calendar Table by going to Modelling > New Table and entering this formula

         

        =CALENDAR(MIN(Data[Sales date]),MAX(Data[Sales date]))

         

        Now you create the Year and Month column as suggested in my previous post.