Forum Discussion

KamK's avatar
KamK
Frequent Visitor
10 years ago
Solved

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 - Transact...
Need Help
  • Eric_Zhang's avatar
    Eric_Zhang
    10 years ago

    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's avatar
KamK
Frequent Visitor
9 years ago

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's avatar
rspags
Frequent Visitor
8 years ago

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's avatar
    jausting
    Frequent Visitor
    8 years ago

    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,