Calculating Days Past Dynamically

[Anonymous]

GOAL: Calculate appropriate days_past as  the year progresses.

Current measure code:

days_past = datediff(DATE(YEAR([year_desired]),1,1),DATE(YEAR([year_desired]),MONTH(MAX('Calendar'[Date])),DAY(EOMONTH(MAX('Calendar'[Date]),0))+1),DAY)

However, this currently calculates the days past in the year to the end of the month.

Example: If we were at 11/03/2022, the days past would return 333. I want it to return the actual days past (306 days).

So, I tried:

days_past = datediff(DATE(YEAR([year_desired]),1,1),DATE(YEAR([year_desired]),MONTH(MAX('Calendar'[Date])),DAY(MAX('Calendar'[Date]))),DAY)

But this then ruins my historical days past. So, when I look back at days past in March, it would show days past since 03/03/2022. Whereas, here, I want it to show  days past since 03/31/2022. So, I want the end-of-month logic for months that have already gone by, but want the current days past in the current month.

Seems fairly simple, but I am having issues making it work with IF statements. Not super familiar with loops in DAX. I have more background with Python.

Thanks for help,

Joel

[v-xiaosun-msft]

Hi @Anonymous ,

I wonder whether I understand your requirement correctly. Do you want to return the number of days from the beginning of the year to that date for each date in the Calendar as below?

Column =
DATEDIFF ( DATE ( YEAR ( 'Calendar'[date] ), 1, 1 ), 'Calendar'[date], DAY )

Final output:

If the above one can't help you get the desired result, please provide some sample data in your tables (exclude sensitive data) with Text format and your expected result with backend logic and special examples. It is better if you can share a simplified pbix file. Thank you.  

Best Regards,
Community Support Team _ xiaosun

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

[Anonymous]

Months	Days Past			Last Refresh Date
1/1/2022	31			11/20/2022
2/1/2022	59
3/1/2022	90
…
11/1/2022	20 + prior days
12/1/2022

See example table. I don't have day logic for much of my data, but do have a specific refresh_date. So, for past months, I want the days through the end of the month summed. Then, for the current month, I want the days in the prior months + the datediff between the current month start date (in this example, 11/1/2022) and the refresh date (11/20/2022).

Does this make sense?

Thanks for your help :),

Joel

[Anonymous]

Any update on the above?