topic Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter in DAX Commands and Tips

Calculate Number of days Between Today and a Date Column in the Report and also Filter

Dashboard_Dev — Sun, 25 Aug 2024 01:36:06 GMT

I'm having difficulty in calculating this. Client wants to calculate the amount of days from today (when the report is run) to an order date, but wants to filter on other column in the data.

This is what I have so far, Calculate Number of Days=DATEDIFF ( MAX ( 'table'[column]), TODAY(), DAY )

Because some orders are canceled, on back order, out of stock, ect., they want to filter by order status (another column in the table) to omit orders that canceled, or out of stock. How do I add a filter to the above measure?

Also the data is in a semantic model, and I am not able to add a calculated column, so this needs to be done with a measure.

Can anyone help me? Is this even possible?

Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter

Irwan — Sun, 25 Aug 2024 02:02:20 GMT

hello Dashboard_Dev

are you looking something like this?

create a new measure with following DAX.

DateDIFF = DATEDIFF(MAX('Table'[Date]),TODAY(),DAY)

Hope this will help.

Thank you.

Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter

shafiz_p — Sun, 25 Aug 2024 05:50:27 GMT

Hi Dashboard_Dev As I can understand, that you have order status column and want to filter out Canceled or out of stock using your provided measure.

You can use slicer as Irwan mentioned or can use filter this visual section in filter pane to filter out unnecessary items.

Any way, if you want to use measure, then wrap your measure inside calculate and mention filter condition. See the example below:

NumberOfDays =

CALCULATE(

DATEDIFF(

MAX('Table'[Date]),

TODAY(),

DAY

'Table'[Status] <> "Canceled",

'Table'[Status] <> "Out of Stock"

)

Hope this helps!!
If this solved your problem, please accept it as a solution.

Best Regards,
Shahariar Hafiz

Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter

Anonymous — Tue, 27 Aug 2024 04:39:27 GMT

Hi Dashboard_Dev

May I ask if your problem has been solved? If it has been resolved, please accept the responses that helped you as a solution.
Both solutions provided by Irwan and shafiz_p are valid, you can follow Irwan's solution to create a slicer to filter orders or follow shafiz_p's solution to filter out orders in the measure.

If you need any other help, please feel free to contact me.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter

Dashboard_Dev — Tue, 27 Aug 2024 19:53:55 GMT

Neither solution solved the issue

Re: Calculate Number of days Between Today and a Date Column in the Report and also Filter

Anonymous — Wed, 28 Aug 2024 05:52:17 GMT

Hi Dashboard_Dev

Could you please provide sample data that covers your issue completely and the expected outcome. This will help us understand your needs more clearly and assist you.

If you are unsure how to upload data please refer to How to provide sample data in the Power BI Forum - Microsoft Fabric Community

A friendly reminder to remember to protect your data privacy.

Best Regards,
Jarvis Tang
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.