Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hello All,
I have table where I am having daily data like date,sales,profit and discount.
My requirement is I need to show a slicer which have values like daily,weekly,monthly and Quarterly.
If the user selects daily I need to show yesterday sales,profit and discount.(27th March)
If the user selects Monthly I need to show last month data.(February 2023)
If the user selects Weekly I need to show last Weekdata. (Mar 19 to Mar 25)
If the user selects Quarterly I need to show last qtr data. (oct - Dec 2022)
So I created a table in power bi with these values and showing as slicer.
In the report I have lot of measures .So I cant calculate above logic for each measure.
So I need a dax which can filter my page for above requirement
I wrote the below one but its a static one for now.
Please help on this.
Not sure how to write the dax to calulate these.
You may run into problems e.g subtracting 1 from current month if your current month is Jan. it would be safer to use something like EODATE to return the last day of the previous month then filter on
year(EODate([Date]-1)) && month(EODate([Date]-1))
Hi,
As per our understandings you are looking for a Filter that can filter your table data on different date selectors like Lastweel, Lastmonth and so on,
You can achieve this by below simple steps,
This is Sample Table
Create a Weeknum and Quarter Column
WeekNumber = WEEKNUM('Sample table'[Date])
Quarter = 'Sample table'[Date].[QuarterNo]
Then Using them create a Resultant column
Needed slicer =
var yesterDay =
IF('Sample table'[Date].[Day]=DAY(NOW())-1,"Yesterdays
sales",BLANK())
var lastMonth =
IF('Sample table'[Date].[MonthNo]=MONTH(NOW())-1,"Last
Month",BLANK())
var lastWeek =
IF('Sample table'[WeekNumber]=MAX('Sample table'[WeekNumber])-1,"Last
Week",BLANK())
var lastQuarter =
IF(MAX('Sample table'[Quarter])-1 =
'Sample table'[Quarter],"Last Quarter",BLANK())
return IF(yesterDay<>blank(),yesterDay,
IF(lastMonth<>BLANK(),lastMonth,
IF(lastWeek<>BLANK(),lastWeek,
IF(lastQuarter<>BLANK(),lastQuarter))))
Use this column in Slicer
Thanks!
Inogic Professional Service Division
An expert technical extension for your techno-functional business needs
Power Platform/Dynamics 365 CRM
Drop an email at crm@inogic.com
Service: http://www.inogic.com/services/
Power Platform/Dynamics 365 CRM Tips and Tricks: http://www.inogic.com/blog/
Hi @SamInogic ,
Thanks for your help.
Could you please make this formula so that I can use as a filter .
I mean I can keep in filters select 1 so that my report can change like my below frmula.
I'd suggest you create a date dimension in PowerQuery or DAX and add calculated columns to indicate current and previous period for week, month, quarter. e.g using DATEDIFF(TODAY(),date dim'[date],WEEK) to get the negative offset in weeks between today's date and each date in your date dimension.
You'll still need to include code in your measure to switch between calculations with different date filters depending on your slicer value but it should simplify your code.
Also you should check out SWITCH() instead of using nested if statements 🙂
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
114 | |
99 | |
81 | |
70 | |
61 |
User | Count |
---|---|
148 | |
116 | |
104 | |
89 | |
65 |