Rolling value calculation

[Anonymous]

Hi,

I have the following question and would like to see if anyone can help. 

Date	Store A	Store B
Nov-21	20	40
Dec-21	20	40
Jan-22	20	40
Feb-22	20	40
Mar-22	20	40

Date	Store	Sales
Nov-21	A	10
Dec-21	B	25
Jan-22	A	30
Feb-22	A	20
Mar-22	A	40

There are 2 set of data, if I select the timeframe Dec-21 to Feb-22, it will gives me the sum of Store A in table 1 within that period (20+20+20) divide the sum of Store A data in table 2 (30+20) = 1.2

So at the end I will need to have 2 measure one is for Store A and one is for Store B, to calulate the rolling value base on the timeline selected.

Thanks for the help!

[Anonymous · Accepted solution]

Hi  @Anonymous ,

Here are the steps you can follow：

1. Create calculated table.

Table =
CALENDAR(MIN('Table1'[Date]),MAX('Table1'[Date]))

2. Create measure.

MeasureA =
var _mindate=MINX(ALLSELECTED('Table'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
return
DIVIDE(
SUMX(FILTER(ALL(Table1),
'Table1'[Date]>=_mindate&&'Table1'[Date]<=_maxdate),[StoreA]),
SUMX(
    FILTER(ALL(Table2),
    'Table2'[Date]>=_mindate&&'Table2'[Date]<=_maxdate&&'Table2'[Store]="A"),[Sales]))

MeasureB =
var _mindate=MINX(ALLSELECTED('Table'),[Date])
var _maxdate=MAXX(ALLSELECTED('Table'),[Date])
return
DIVIDE(
SUMX(FILTER(ALL(Table1),
'Table1'[Date]>=_mindate&&'Table1'[Date]<=_maxdate),[StoreB]),
SUMX(
    FILTER(ALL(Table2),
    'Table2'[Date]>=_mindate&&'Table2'[Date]<=_maxdate&&'Table2'[Store]="B"),[Sales]))

3. Result:

If you need pbix, please click here.

Rolling value calculation.pbix

Best Regards,

Liu Yang

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly

[Anonymous]

Hi Yang,

Thanks for the quick response.

The equation is working but if I visualize it in a line chart, it only show 1 result, is there any possibility that it can show also the cumulative trend from 2021 to 2022?

I tried to do this in excel as exmaple, not sure if it works in power BI

Thank you.

[amitchandak]

@Anonymous , Create a common Date  table and then you can have measures like

Divide(Sum(Table1[StoreA]), calculate(Sum(Table2[Sales]), filter(Table2, table2[Store] ="A") ) )

Or you can unpivot first table have store there and common store table too

refer

https://amitchandak.medium.com/power-bi-when-i-asked-you-to-create-common-tables-a-quick-dax-solution-8e3eccb41bda

https://radacad.com/pivot-and-unpivot-with-power-bi

[Anonymous]

Hello,

Is there anyway not using common dates? Since my sales data contains a board range of dates and cannot suit into the store table.

Thank you.