Forum Discussion

Chanleakna123's avatar
Chanleakna123
Icon for Post Prodigy rankPost Prodigy
6 years ago

Calculate 1 Measure with Parameter based on selected between 2020 & 2021

hi , i have below table , 

and i gonna have 2 parameter , 1 in 2020 and 1 in 2021 , 

in 2020 ,i gonna put parameter growth on any Percentagees based on selected , 

in 2021 , i gonna put parameter growth on any Percentages based on selected too. 

 

the thing is : i only would like to use 1 measure between 2020 and 2021 , because my graph gonna visual from Jan 2020 to Dec 2021 only with 1 Bar Chart. 

 

 

 

Show and Tell

7 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    6 years ago

    Chanleakna123 , Prefer to have a date table with the year in it .I am not sure when you say slicer they are independent or from date table assume they are independent

     

    M1 = calculate(Sum(Table[Volume]), filter(Date,Date[Year] =selectedvalue(Param1[Param1])))

    M2= calculate(Sum(Table[Volume]), filter(Date,Date[Year] =selectedvalue(Param1[Param1]) -1 ))

     

    M3= calculate(Sum(Table[Volume]), filter(Date,Date[Year] =selectedvalue(Param2[Param2])))

    M4= calculate(Sum(Table[Volume]), filter(Date,Date[Year] =selectedvalue(Param2[Param2])-1))

     

    You can diff between M1, M2 or M1 , M3

     

    diff% =divide(M1-M2,M2)

    • Chanleakna123's avatar
      Chanleakna123
      Icon for Post Prodigy rankPost Prodigy
      6 years ago

      hi , it's refering to Year , once the growth in 2020  slices to 15% , it will run to 15% Growth the formula , 

      and in 2021 , it will calculated from 2020 , with another parameter with the growth 20% or sth , therefore it will calculated from 2020 base volume . 

      and i only want 1 measure which in order to show only 1 bar chart from Jan 2020 - Dec 2021. 

       

       

      • amitchandak's avatar
        amitchandak
        Icon for Super User rankSuper User
        6 years ago

        Chanleakna123 , you can use datesmtd? example 

        MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
last MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-1,MONTH))))
last year MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-12,MONTH)))
last year MTD (complete) Sales =  CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(ENDOFMONTH(dateadd('Date'[Date],-12,MONTH))))
Month behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Month))


diff = [MTD Sales]-[last MTD Sales]
diff % = divide([MTD Sales]-[last MTD Sales],[last MTD Sales])