Forum Discussion
Defining basis month or year and comparing
Hi guys,
I have a table of sales figures with dates (monthly, quarterly, yearly) and I'd like to choose a basis month or year (which will be 100 and should change according to selected month or year but basis should be 100) then compare it as in below. Many thanks!
Bascially I have used a New Table of Dates to slice the original Table
A New Table (Calculated Table) was created from the Modeling Tab>>> NEW TABLE
New_Table = ALL ( TableName[Date] )
Then following MEASURE was added to Original Table
Index = VAR selectedmonthSales = CALCULATE ( SUM ( TableName[Sales] ), FILTER ( ALL ( TableName ), TableName[Date] = SELECTEDVALUE ( 'New_Table'[Date] ) ) ) RETURN IF ( SELECTEDVALUE ( TableName[Date] ) = SELECTEDVALUE ( 'New_Table'[Date] ), 100, SELECTEDVALUE ( TableName[Sales] ) * 100 / selectedmonthSales )Using Slicer from New Table, now you can get the required Index Measure in your Original Table
11 Replies
- Zubair_Muhammad
Community Champion
- Zubair_Muhammad
Bascially I have used a New Table of Dates to slice the original Table
A New Table (Calculated Table) was created from the Modeling Tab>>> NEW TABLE
New_Table = ALL ( TableName[Date] )
Then following MEASURE was added to Original Table
Index = VAR selectedmonthSales = CALCULATE ( SUM ( TableName[Sales] ), FILTER ( ALL ( TableName ), TableName[Date] = SELECTEDVALUE ( 'New_Table'[Date] ) ) ) RETURN IF ( SELECTEDVALUE ( TableName[Date] ) = SELECTEDVALUE ( 'New_Table'[Date] ), 100, SELECTEDVALUE ( TableName[Sales] ) * 100 / selectedmonthSales )- Zubair_Muhammad
Using Slicer from New Table, now you can get the required Index Measure in your Original Table