cancel
Showing results for
Did you mean:

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a \$100 discount. Register Now

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
Employee

@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
Employee

@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"
)```
Frequent Visitor

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

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.
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)

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

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

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

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,

Anonymous
Not applicable

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.

Frequent Visitor

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
Not applicable

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.

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.

Announcements

#### Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

#### Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

#### Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

#### Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors