Date filter needed where sales data by year held in different tables

[PeteGordonAG]

I have sales data held in separate tables by financial year - I had to split out the data as my server could not cope.

I have a date table which uses the date to return a Future, Today or Past value.

However all the previous year info is set in the past.

I therefore need to move the control date forward each day to show only the month to date info.

Can a filter be set so the Day and date is used only to filter on what values pull through to avoid the manual work.

[amitchandak · Accepted solution]

@PeteGordonAG , You need control to bring in data in Power Query or to default value in slicer 

Power query you can use local now -DateTime.LocalNow()

If you default slicer, you have to create a text column 

Is Today = if([Date]=TODAY(),"Today",[Date]&"")

and save slicer on today 

[Jaywant-Thorat · Accepted solution]

Understanding the Scenario: You want Power BI to automatically show only Month-to-Date (MTD) values without manually adjusting a date every day, even though your sales data is split into multiple year-wise tables.
Solution: Create MTD measure

Sales MTD = CALCULATE(SUM('Sales'[Amount]),DATESMTD('Date'[Date]))

This automatically filters all tables, current year or past years, based on your Date table.

No need to adjust any "control date". Always updates daily.
Works even with separate yearly sales tables (as long as all relate to one Date table).

Did I answer your question? Mark my post as a solution! This will help others on the forum!

Appreciate your Kudos!!

Jaywant Thorat | MCT | Data Analytics Coach
Linkedin: https://www.linkedin.com/in/jaywantthorat/

[v-menakakota]

Hi @PeteGordonAG ,

Thanks for reaching out to the Microsoft fabric community forum. 

I would also take a moment to thank @amitchandak   , for actively participating in the community forum and for the solutions you’ve been sharing in the community forum. Your contributions make a real difference.

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team  

[v-menakakota]

Hi @PeteGordonAG ,

I hope the above details help you fix the issue. If you still have any questions or need more help, feel free to reach out. We’re always here to support you .

Best Regards, 
Community Support Team 

[amitchandak · Accepted solution]

@PeteGordonAG , You need control to bring in data in Power Query or to default value in slicer 

Power query you can use local now -DateTime.LocalNow()

If you default slicer, you have to create a text column 

Is Today = if([Date]=TODAY(),"Today",[Date]&"")

and save slicer on today