Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and more. Get started

Reply
frankGB
Frequent Visitor

Dynamic DAX date table based on a reference date from a Filter

Hi,

 

I am trying to create a dynamic date table in DAX with calcualted dates based on a in a filter selected reference date. 

 

Let's say the selected reference date in the filter visual is 15/05/2022 based on the selected vale from column 'Filter_Date'[Date] in the date table.

 

Using this reference date, I want to calculate a date table with a few calcualted dates. To be included dates in the calculated table should simply be

 

selecteddate, selecteddate - 7 days, selecteddate-14 days etc. 

 

Output table would look like the below

 

CALCULATED DATE

15/05/2022

08/05/2022

01/05/2022

....

 

Ultimatelty I want to use the column in a visual as the legend values. 

 

Frank

4 REPLIES 4
frankGB
Frequent Visitor

I was hoping I can create a table rather then measures for each of the calculated days then.

 

Are you aware of any soltion for a table?  

Hi @frankGB ,

 

Is this table a calculated table? If so, it is not possible. The calculation table is calculated when the model is refreshed, and user operations on the report do not affect the calculated table. (e.g. using a slicer)

vcgaomsft_0-1654742783152.png

Hope this will help you.

 

Best Regards,
Gao

Community Support Team

 

If there is any post helps, then please consider Accept it as the solution to help the other members find it more quickly. If I misunderstand your needs or you still have problems on it, please feel free to let us know. Thanks a lot!

How to get your questions answered quickly -- How to provide sample data

yes, it is a calculated table. I could also create it as a static table in M and then probably do the Max and Min calculations based on a reference date to find the dates to be included in the visual. 

 

I'll give that a try.

amitchandak
Super User
Super User

@frankGB , You have to create a measure and filter

 

calculate(sum(Table[Value]), filter(Table, Mod(Datediff(selectedvalue(Date[Date]), Date[Date], day),7) =0))

 

or

 

calculate(sum(Table[Value]), filter(Table, Mod(Datediff(selectedvalue(Date[Date]), Date[Date], day)+1,7) =0))

Helpful resources

Announcements
Europe Fabric Conference

Europe’s largest Microsoft Fabric Community Conference

Join the community in Stockholm for expert Microsoft Fabric learning including a very exciting keynote from Arun Ulag, Corporate Vice President, Azure Data.

July 2024 Power BI Update

Power BI Monthly Update - July 2024

Check out the July 2024 Power BI update to learn about new features.

July Newsletter

Fabric Community Update - July 2024

Find out what's new and trending in the Fabric Community.