Forum Discussion

kolturra's avatar
kolturra
Icon for Helper IV rankHelper IV
5 years ago

% Comparison from Pervious Year and Month Measure

Hi Experts,

 

I need a help with regards to % Comparison from Pervious Year and Month Measure and both should come from same visual using drill down option.

 

we can use any dummy data to achieve my graphs

 

Year Wise Presentation

When i drill down i should view Month Wise View like below

 

Thanks,
Raghu

 

 

 

help in dax Function
Help with DAX Measure

3 Replies

  • Fowmy's avatar
    Fowmy
    Icon for Super User rankSuper User
    5 years ago

    kolturra 

    I am not sure if I read your question correctly. If you want to drill down to the next level, you click on the double arrow.


    Make sure your fields are stacked as below:

     

     

     

    • kolturra's avatar
      kolturra
      Icon for Helper IV rankHelper IV
      5 years ago

      Sorry I think I did not give proper communication in my earlier message. We need to create measure for % comparison with previous year and month

      Year wise Visual.

      1. Year wise it has values of each year in bar.
      2. % Comparison with previous year which is line chart - This we have to achieve with measure

      Drill Down Visual - Once we click on specific year, we will drill down to Month level.

      1. Months wise it has values of each month in bar.
      2. % Comparison with previous month which is line chart - This we have to achieve with measure

       

       

      My data is in below format

       

      Column 1 - Month - Jan 2020 to Dec 2021

      Column 2  - Value - between 100 to 200

       

      Not able to paste table because of some error, if you create this table then my data will be ready

      • Fowmy's avatar
        Fowmy
        Icon for Super User rankSuper User
        5 years ago

        kolturra 

        I created the sample data based on your explanation for the years 2020 to 2022, add a date table to use Time Intelligence functions, created the following single measure to calculate % variance for both year and month level. I also attached the PBIX file for you to learn.

        vs Previous Period  % = 
var __Curr = [Amount]
var __PreYR = 
    CALCULATE(
        [Amount],
         DATEADD( Dates[Date] , -1,YEAR)
    )
var __PreMO = 
     CALCULATE(
        [Amount],
        DATEADD( Dates[Date] , -1,MONTH)
    )

var result = 
    IF(  
        ISFILTERED(Dates[Year-Month]),
        DIVIDE( __Curr - __PreMO , __PreMO ),
        DIVIDE( __Curr - __PreYR , __PreYR )
    )
return
    result