How to display reporting dates for weekly, MTD and YTD

[rahulvarmabi]

Hi,

I have data where it refresh on regular basis.
I want to display the dates on basis of refreshed date. For Weekly, MTD and YTD.

For example
Data is refreshed today that is today 5/5/2022
Then for weekly it should dates from last 7 days and refresh date i.e.,
28/4/2022 - 5/5/2022
In same way for MTD 1/5/2022 - 5/5/2022
For YTD 1/1/2022 - 5/5/2022

How do we achieve this?

 

[Chihiro]

I'd recommend uploading sample workbook (with your data model). If that's not possible, you can have small desensitized sample in a workbook and explain data relationship.

Without that, bit difficult to give you specific help.

But in general, you'd use Calendar/Date Dimension table and use Time intelligence functions.
Such as...
TOTALYTD() or TOTALMTD()
TOTALYTD function (DAX) - DAX | Microsoft Docs


See threads below for similar topic.
Current month vs prior year by product line | Chandoo.org Excel Forums - Become Awesome in Excel

For Week there is no specific DAX function for that (though what you describe isn't really Weekly, but last 7 days).
You can just use CALCULATE() to filter context.
Exact formula will depend on your data structure (if you have calendar dimension table or not, is weeknum defined in that table etc).

=CALCULATE(SUM([SomeColumn]), FILTER(CalendarTable,[DateColumn]>=(Today() - 7)&&[DateColumn]<=Today()))

 

[Chihiro]

More readings.
Time Intelligence Function | Chandoo.org Excel Forums - Become Awesome in Excel
PowerPivot Calendar Table Question | Chandoo.org Excel Forums - Become Awesome in Excel

 

[rahulvarmabi]

I just need to display the dates over there no filtering of data is needed.

 

[Chihiro]

Hmm? I don't get you. Display dates without any context? Is it PowerBI, PowerPivot or something else that you are using?

What method are you trying to do this in? Using DAX? PowerQuery? What visual?

Do you actually need each date value as distinct value? Or as string represetation? etc.

Not enough info to help you, I'm afraid.