Starting December 3, join live sessions with database experts and the Microsoft product team to learn just how easy it is to get started
Learn moreGet certified in Microsoft Fabric—for free! For a limited time, get a free DP-600 exam voucher to use by the end of 2024. Register now
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
Solved! Go to Solution.
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" )
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" )
Worked a treat , Thank you very much !!!!!
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
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
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
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.
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
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.
Starting December 3, join live sessions with database experts and the Fabric product team to learn just how easy it is to get started.
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount! Early Bird pricing ends December 9th.
User | Count |
---|---|
87 | |
87 | |
87 | |
67 | |
49 |
User | Count |
---|---|
135 | |
113 | |
100 | |
68 | |
67 |