Forum Discussion
Solved
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 y...
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
Many thanks! Is ALL function limited with only one year or can i add more date? I've tried this in power pivot but "a table of multiple values supplied where a single value is expected" error showed up.
Zubair_Muhammad
Community Champion
Community ChampionYes, in the new table.
- Zubair_Muhammad
- Zubair_Muhammad
In Excel you can simply copy the Dates Column from your Original table and paste it as NEW TABLE and then Add it to the DATA MODEL.
See the Excel file I attached above