cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
KamK
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
Eric_Zhang
Microsoft
Microsoft

@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"
)

View solution in original post

11 REPLIES 11
Eric_Zhang
Microsoft
Microsoft

@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

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

bkoo
Frequent Visitor

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

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 🙂

 

Regards

 

Kam

rspags
Frequent Visitor

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

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

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,

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

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

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

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.

Helpful resources

Announcements
September Update

Check it Out!

Click here to learn more about the September 2022 updates!

Power BI Dev Camp Session 26

Check it Out!

Mark your calendars and join us on Thursday, September 29 at 11a PDT for a great session with Ted Pattison!

Top Solution Authors