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

[Chanleakna123]

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. 

[amitchandak]

@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]

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. 

[Anonymous]

Hi @Chanleakna123 ,

Could you please check if the following screen shot is what you want?

If the above one is not what you want, please provide your actual desired result and explain more details on it with examples(include calculation logic and what that measure is used for etc.). 

Best Regards

Rena

[Chanleakna123]

hi all , 

i have used Parameter in my report , therefore this slicer only use in 2021 , it means , when i slice to 20 , it will calculate from :

BP 2021 = Sales Volume in 2020 * 20% ( how can i calculate the BP 2021 with the sames Volume in 2020 with the Parameter i set 20 ? PS : the parameter of 20 is up and down based on the User selected ) 

and the thing is , in my manual visualization , i can use the Axis in Jan 2020 to Dec 2021 , which is in the 1 bar chart Only , is it possible that the BP 2021 , can also combine with the data in 2020 in 1 Bar Chart ?

PS : i have actual sales Volume 5 years back then from 2015-2020 , Now would like to see 2021 by using the sales volume in 2020 as mentioned above with using  parameter of growth % .

[Anonymous]

Hi @Chanleakna123 ,

You can create a measure as below:

BP 2021 =
VAR _v2021 =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER (
            'Table',
            'Table'[Date]
                = DATE ( YEAR ( MAX ( 'Calendar'[Date] ) ) - 1, MONTH ( MAX ( 'Calendar'[Date] ) ), DAY ( MAX ( 'Calendar'[Date] ) ) )
        )
    )
VAR _vb2021 =
    CALCULATE (
        SUM ( 'Table'[Volume] ),
        FILTER ( 'Table', 'Table'[Date] = MAX ( 'Calendar'[Date] ) )
    )
RETURN
    IF (
        YEAR ( MAX ( 'Calendar'[Date] ) ) = 2021,
        _v2021 * [Parameter Value],
        _vb2021
    )

Best Regards

Rena

[Chanleakna123]

@Anonymous  @amitchandak 

[amitchandak]

@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])