Forum Discussion
Create a Calculated Column in a matrix
I am not really sure if there is an easier way. I have two date tables which I use as slicers allowing me to select two periods to compare and then I have a master date table where I create my calculations, e.g. sales for period 1 and sales for period 2
Hope this makes sense and apologies if I have misunderstood what you are trying to do.
Edit: I have attached a screenshot of how my matrix looks if that helps.
ShonaHi Shona
Thanks for that :smileyhappy:
I have managed to do it by creating a seperate date table then by creating a previous year measure and % change measure. When I now select my required month by way of a filter it will show this month for the current year and the previous year coloum will filter to show the sales for that period last year and the % variance between these two months.
Thanks for your help...
I have done something like this before by creating two date tables for your filtered dates (with no relationship set) and then using filter and crossjoin to get the filtered dates.
Example create following measure for sales in each of your selected date ranges:
CALCULATE(SUM(.....),
FILTER (
CROSSJOIN ( VALUES ( SalesCalendar[date] ), VALUES ( 'period 1'[date] ) ),
SalesCalendar[date] = 'period 1'[date]
))
SalesCalendar is my master date table and then I have another two tables for the filtered dates period1 and period2. You can repeat above measure for your second date range and then create a measure to do the difference between the two.
I am no DAX expert so there may be an easier way of doing this or I may have completely misunderstood :)
Shona
Hi Shona
Thanks for the reply .
I want to be able to use the filter/slicer , so would I then have to create a measure for every month , year and quarter in my data along with similar date tables?
I hope there is an easier way :smileyhappy:
Kind Regards