Forum Discussion
Rolling value calculation
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!
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
4 Replies
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
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
