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.
Greetings,
1# I am looking to create a formula that will take the date selected by the user and go back 30 days and go forward 30 days and pull the results within that 60-day time frame. I need it only to affect that one visual and none of the others.
2# Also, I am trying to remove the date filter and slicer affecting a visual. I have tried format>edit interactions>none (on the visual itself). However, when I go back to the home tab and click on the filter icon the dates are still affecting the visual. What I am trying to do its remove the date affecting that visual.
For the dynamic date range try this
60 day range =
var maxdate = max(table with date[date]
return
if(datediff(calendar[date],maxdate , day) in generateseries(-30, 30, 1), 1, 0)
then use this measure in your date filter lane and select 1 and it will work.
let me know if that make sense
For answer #2 when you are in edit mode you have to select each visual on your dashboard and then turn off interactions with the visual you don't want to change.
Can you state specifically where edit mode is? I am thinking under transform data.
i can even do a quick teams call with you and walk you through it
Let schedule.
Let me know times and we can try to connect tomorrow
I am off this Thursday and Friday.
Not sure what you mean by edit mode. where is edit mode?
when you select a visual and then format in the menu bar then select edit interactions
Yes,
I did that however it is still there. I do not understand. I posted the picture of it at the beginning of this thread.
Hi @Anonymous
Create a date table which doesn't have a relationship with your tables.
Then add the date from this date table into a slicer,
Create measures
selected date = SELECTEDVALUE(date1[Date])
Measure = CALCULATE(SUM('Table 2'[value]),FILTER('Table 2',ABS(DATEDIFF([selected date],[Date],DAY))<=30))
Best Regards
Maggie
Community Support Team _ Maggie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
I made a different date table and the slicer. This is the error I am getting
can you send me a copy of the PBIX file and then i can do a quick fix, i can make sure the table is connected correctly and then add the measure
I wish i could but it has company information.
@Anonymous , Not very clear are you looking for
Rolling 30 day = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],MAX(Sales[Sales Date]),-30,Day))
Rolling 3 till last month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 2 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-2,month)),-3,MONTH))
Rolling 3 till last 1 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-1,month)),-3,MONTH))
Rolling 3 till last 3 month = CALCULATE(sum(Sales[Sales Amount]),DATESINPERIOD('Date'[Date],ENDOFMONTH(dateadd(Sales[Sales Date],-3,month)),-3,MONTH))
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/
See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-YTD-LYTD-Week-Over-Week/m-p/1051626#M184
Appreciate your Kudos.
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 |
---|---|
112 | |
97 | |
84 | |
67 | |
60 |
User | Count |
---|---|
150 | |
120 | |
99 | |
87 | |
68 |