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.
I have used a date table to connect the data from 2 different files and its working fine. However, when I publish and share the dashboard with other stakeholders, it's gets confusing from them till what date its showing the values. How can I hide the future dates from the slicer?
On the other hand, if there is only 1 excel file and no date table, there is no such problem.
Please guide!
Thanks
Solved! Go to Solution.
Hi @ShuchiSharma ,
Firstly, in your below formula, the position of the closing parenthesis of the MAX function is wrong, it should be
D=CALENDAR(MAX('Date'[Date]),today())
In your new Date table formula, you don't need to calculate the min and max year because the calendarauto function is calculated automatically based on data in the model. Modify it like this.
Date =
ADDCOLUMNS (
FILTER ( CALENDARAUTO (), [Date] <= TODAY () ),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
This table will get your expected date period.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi guys,
Still need help! Can anyone please help here with the above query?
Thanks
Hi Shishir,
Thanks for the reply. I have tried but seems like doing it somewhere wrong. Can you please have a look?
Using the below, I created the date table:
And there are only three tables:
Thanks
Hi @ShuchiSharma ,
Firstly, in your below formula, the position of the closing parenthesis of the MAX function is wrong, it should be
D=CALENDAR(MAX('Date'[Date]),today())
In your new Date table formula, you don't need to calculate the min and max year because the calendarauto function is calculated automatically based on data in the model. Modify it like this.
Date =
ADDCOLUMNS (
FILTER ( CALENDARAUTO (), [Date] <= TODAY () ),
"Calendar Year", "CY " & YEAR ( [Date] ),
"Month Name", FORMAT ( [Date], "mmmm" ),
"Month Number", MONTH ( [Date] )
)
This table will get your expected date period.
I attach my sample below for reference.
Best Regards,
Community Support Team _ kalyj
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Thanks for the suggestion jaipal. However, this doesn't include the past dates. I need all the dates till today to compare the results.
Hello @ShuchiSharma,
If you dont need to see future dates data, I would suggest remove it from your date dimension and your fact tables.
You can use Calendar(Max(date column from your fact table), today()) to get table with dates only till today
or
You can add a flag(Calculated column) in your date dimension and use it as a report/visual level filter.
Flag=if(DimDate[Date] <= Today(),1,Blank())
apply Flag=1 as a report/visual level filter.
Please mark it as answer if it solves your issue. Kudos are also appreciated..
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 |
---|---|
113 | |
99 | |
75 | |
73 | |
49 |
User | Count |
---|---|
145 | |
109 | |
109 | |
90 | |
64 |