Dynamic Column calculation based on Slicer Selection

[KamK]

Hi Everyone

Im hoping someone can help me with this .

Note - I have some experience using excel and have just started out learning DAX

Scenario - 

I have the following Tables -

Transaction data -  with sales figures by region / product type / date etc

DateKey - Has dates including all possible dates in my transaction data and extra cloumns for financial year / calendar year etc

 Now  I have created a table with two cloumns (disconnected to anything else) that I would like to use as a slicer. the Values are "12 Months", "24 Months", "36 Months" & Overall . They have a corresponding  PERIOD ID's of 1/2/3/4 on the same table. 

The idea is to use this slicer to drive a a column chart showing Sales figures by month  . ie. 12 month sales /24 month sales etc

Work Done so far - I created 4 extra calculated columns on my date table with the correspoding names to my slicer table i.e 12 months / 24 months /36 months/ Overall  . Thier values are either 0 or 1 based on whether the date falls within the selected period for e.g = for the 12 month column I have  IF(DateKey[Date]>=EOMONTH(MAX(DateKey[Date]),-12)+1,1,0)

What I would like to do is have one more calculated column lets call it ("Relative Date")  on my date table that is dynamic based on what I select on my Slicer . If I select 12 Months then I get the corresponding value from the 12 month column , so if the date falls within the 12 months i get a 1 else I get a 0.

This way I could create a column chart with Sales  and Month with where the visual filter = 1 ,  it would show all dates for the time period

I have tried the following formula by reading through the forums, But it always results in 0 regardless of selection 

Relative Date = IF(HASONEVALUE(Periods[PERIOD ID]),SWITCH(FIRSTNONBLANK(Periods[PERIOD ID],Periods[PERIOD ID]),1,DateKey[12 Months],2,DateKey[24 Months],3,DateKey[36 Months],4,DateKey[Overall]),0)

Any help would be appreciated , Also feel free to tell me If Im completely off track and perhaps provide a simpler solution.

Regards

Kam

[Eric_Zhang · Accepted solution]

@KamK

Calculated columns are initialized regardless of Slicers.

If you'd like to filter your data via the selected value in a slicer, instead of a calcualted column, use a measure as below to filter in DAX.

selectedValue =
IF (
    ISFILTERED ( FILTERTABLE[Value] ) && HASONEVALUE ( FILTERTABLE[Value] ),
    LASTNONBLANK ( FILTERTABLE[Value], 0 ),
    "a default value"
)

[KamK]

Worked a treat , Thank you very much !!!!!

[bkoo]

Hi KamK. Can you share your final DAX for your challenge? I am having something similar and would be great to know exactly what you've done. Thanks a lot.

[KamK]

Hi @bkoo

The Accepted solution did work somewhat if I recall correctly but presented some other issues for me .  Since then the Power BI

team have released date based slicers so the the following solution is somewhat redundant .

My main requirement was to essentially have a easy way to slice revenue data based on last latest month / last 12 months  / last 24 months etc

Anyway what I did was I first created a simple table screenshot below (Just made one in excel and imported it)

Next  I created a another table with the following dax

- Note this assumes you have a separate date table , In my case that was "DateKey" , and a column with all relavant dates called "date"

"DatePeriod =
UNION (ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('DateKey',DATESBETWEEN('DateKey'[Date],EOMONTH(MAX(DateKey[Date]),-1)+1,[Max Date])), 'DateKey'[Date]),"Period","Last Month"),ADDCOLUMNS( SUMMARIZE( CALCULATETABLE('DateKey',DATESBETWEEN('DateKey'[Date],EOMONTH(MAX(DateKey[Date]),-12)+1,[Max Date])), 'DateKey'[Date]),"Period","Last 12 Months") , ADDCOLUMNS(SUMMARIZE( CALCULATETABLE('DateKey' , DATESBETWEEN(DateKey[Date],EOMONTH(MAX(DateKey[Date]),-24)+1,[Max Date])), 'DateKey'[Date]),"Period","Last 24 Months") , ADDCOLUMNS(SUMMARIZE( CALCULATETABLE('DateKey' , DATESBETWEEN(DateKey[Date],EOMONTH(MAX(DateKey[Date]),-36)+1,[Max Date])) , 'DateKey'[Date]),"Period","Last 36 Months"),ADDCOLUMNS(SUMMARIZE(CALCULATETABLE('DateKey'),'DateKey'[Date]),"Period","Overall")) "

Once done it should like the following

 Form a relationship between the two tables .

Create a slicer based on Sales Period and you should be able to drive your relavant charts .

If this doesnt help in anyway , please give some further details and I will try to help

Hope this helps 🙂

Regards

Kam

[rspags]

I have tried your solution but am running into an issue.  My dates are not by month like your but rather by week.  Is there a way to update your formula to account for this?  Also, i am trying to look at the data in a slicer by Last 4 Wks, Last 12 Wks, Last 26 Wks, Last 52 Wks, MTD and YTD periods.

Thanks for any help you can provide.  I have been trying to figure this out for a while not. 

Ryan

[rspags]

I have tried your solution but am running into an issue.  My dates are not by month like your but rather by week.  Is there a way to update your formula to account for this?  Also, i am trying to look at the data in a slicer by Last 4 Wks, Last 12 Wks, Last 26 Wks, Last 52 Wks, MTD and YTD periods.

Thanks for any help you can provide.  I have been trying to figure this out for a while not. 

Ryan

[jausting]

Hi

I dont think its possible to calculate a column based on a sliver value. I have been looking for a solution but unable to find one.

I have been wanting to sum the sales for the past 12 months based on a slicer selected date and cannot find a slution anywhere,

[Anonymous]

Hi Kam,

I am somehow not able to get this to work. I have the Date table, also the DatePeriod table now. I joined Date to DatePeriod (1-many). And my Calls table to Date (many to one). My slicer is based on DatePeriod and shows the Last Month etc. fine. It even shows the corresponding (correct) data from Date table (in a table visual with just the Date and DatePeriod fields).

However, somehow my other visuals don't get that filter from slicer. Is it because the Calls are not directly related to the DatePeriod (they are connected via Date table). Thanks for your help.

[KamK]

HI @Anonymous

Apologies for the late reply , Assuming you have used to the same naming convention as I have in the example the slicer should be based on Sales Period

If you still have issues , are you able to share an example file ?

cheers

Kam

[Anonymous]

Hi Kam,

I am somehow not able to get this to work. I have the Date table, also the DatePeriod table now. I joined Date to DatePeriod (1-many). And my Calls table to Date (many to one). My slicer is based on DatePeriod and shows the Last Month etc. fine. It even shows the corresponding (correct) data from Date table (in a table visual with just the Date and DatePeriod fields).

However, somehow my other visuals don't get that filter from slicer. Is it because the Calls are not directly related to the DatePeriod (they are connected via Date table). Thanks for your help.

[bkoo]

Thanks KamK. I will try your solution and let you know. A few month ago I've revolved this need using switches in DAX, however I think your solution should be cleaner than mine.