cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
KamK Frequent Visitor
Frequent Visitor

Dynamic Column calculation based on Slicer Selection

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

 

 

 

1 ACCEPTED SOLUTION

Accepted Solutions
Moderator Eric_Zhang
Moderator

Re: Dynamic Column calculation based on Slicer Selection

@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"
)
11 REPLIES 11
Moderator Eric_Zhang
Moderator

Re: Dynamic Column calculation based on Slicer Selection

@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 Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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

bkoo Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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 Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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)

Periods - Power BI.PNG

  

 

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

 

DatePeriod table.PNG

 

 Form a relationship between the two tables .

 

 

Relationships.PNG

 

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 Smiley Happy

 

Regards

 

Kam

bkoo Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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.
ninadtambe Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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.

ninadtambe Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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 Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

HI @ninadtambe

 

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

 

Capture.PNG

 

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

 

cheers

 

Kam

rspags Frequent Visitor
Frequent Visitor

Re: Dynamic Column calculation based on Slicer Selection

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