hello everyone i have this table below i made below:
the behavior of the table is if i select a date on the slicer, it will show from start date till selected date
what i want is to add an index to the table according to the slicer and by product
so desired result is :
Product | Year-Month | Sales Measure | Index |
A | Sep-20 | 10 | 1 |
A | Oct-20 | 8 | 2 |
A | Nov-20 | 0 | 3 |
A | Dec-20 | 5 | 4 |
A | Jan-21 | 0 | 5 |
B | Jun-20 | 0 | 1 |
B | Jul-20 | 0 | 2 |
B | Aug-20 | 4 | 3 |
B | Sep-20 | 9 | 4 |
B | Oct-20 | 6 | 5 |
B | Nov-20 | 0 | 6 |
B | Dec-20 | 0 | 7 |
B | Jan-21 | 0 | 8 |
but it should change when i seelct another date from slicer anotehr example :
desired result:
Product | Year-Month | Sales Measure | Index |
A | Sep-20 | 10 | 1 |
A | Oct-20 | 8 | 2 |
B | Jun-20 | 0 | 1 |
B | Jul-20 | 0 | 2 |
B | Aug-20 | 4 | 3 |
B | Sep-20 | 9 | 4 |
B | Oct-20 | 6 | 5 |
Note that index should be by date(Year-Month) sort as in the example shown
is this possibe?
@tamerj1 @FreemanZ
Solved! Go to Solution.
Hi,
I am not sure how your data model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Sales on or before slicer: =
VAR _slicerdate =
MAX ( 'Slicer table'[Date] )
RETURN
CALCULATE (
SUM ( Sales[Sales] ),
KEEPFILTERS ( 'Calendar'[Date] <= _slicerdate )
)
Index expected result: =
VAR _startdate =
CALCULATE ( MIN ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _monthyearbyproducttable =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startdate )
)
RETURN
IF (
NOT ISBLANK ( [Sales on or before slicer:] )
&& HASONEVALUE ( 'Calendar'[Month-Year] ),
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
_monthyearbyproducttable,
ORDERBY ( 'Calendar'[Month-Year sort], ASC )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Hi,
I am not sure how your data model looks like, but I tried to create a sample pbix file like below.
Please check the below picture and the attached pbix file.
Sales on or before slicer: =
VAR _slicerdate =
MAX ( 'Slicer table'[Date] )
RETURN
CALCULATE (
SUM ( Sales[Sales] ),
KEEPFILTERS ( 'Calendar'[Date] <= _slicerdate )
)
Index expected result: =
VAR _startdate =
CALCULATE ( MIN ( Sales[Date] ), ALL ( 'Calendar' ) )
VAR _monthyearbyproducttable =
CALCULATETABLE (
SUMMARIZECOLUMNS ( 'Calendar'[Month-Year], 'Calendar'[Month-Year sort] ),
FILTER ( ALL ( 'Calendar' ), 'Calendar'[Date] >= _startdate )
)
RETURN
IF (
NOT ISBLANK ( [Sales on or before slicer:] )
&& HASONEVALUE ( 'Calendar'[Month-Year] ),
COUNTROWS (
WINDOW (
1,
ABS,
0,
REL,
_monthyearbyproducttable,
ORDERBY ( 'Calendar'[Month-Year sort], ASC )
)
)
)
If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.
Join us for a free, hands-on Microsoft workshop led by women trainers for women where you will learn how to build a Dashboard in a Day!