Span period by year and month to display on column chart

[T_BZH]

Hi,

This is my very fisrt post, so be indulgent 🙂

I am working on a very basic problem since days now, so I would appreciate to get the community input on this :

The main goal is to fill a column chart, by year and month based on a very simple data.

I have a table with StartDate_1, EndDate_1, StartDate_2, EndDate_2

These dates can span over several months, even years

I need to get the cumulative amount of days on each period

Example for the first two rows (Year-Month-Day) :

Start	End	Sep-23	Aug-23	Jul-23	Jun-23	May-23	Apr-23	Mar-23
2023-07-11	2023-09-08	7	31	20
2023-02-28	2023-07-03			3	30	31	30	31
	Total	7	31	23	30	31	30	31

Then we have to add the amount of days for the second set of Start and End Dates

Finaly, its getting worst here, as I am not even able to reproduce this logical in Power BI, I have to make this as a running total on a 12 months period!

Could you please advise?

Thanks!

[T_BZH · Accepted solution]

Hi, 

Here is the solution I found :

- Create a Date table based on the max and min values from the entire periods.

- Create a new table using the CROSSJOIN function filtering dates when within the period only (this adds a new row for each day in a period).

- Create a measure to count the rows, as per the context behavior, this provides the number of days per year, month or whatever you use in your chart or table.

Don't forget to make the right relations between all the tables (including the new Date and Crossjoin tables).

This is it!

Hope this will help some of you as I spent days on it!

[amitchandak]

@T_BZH , check if this between month logic can can help

Power BI Dax Measure- Allocate data between Range: https://youtu.be/O653vwLTUzM

Matrix as Project plan Visual: https://youtu.be/R25QoiyoSVs

[T_BZH]

Hi,

Thanks for the input, this does not really address my issue, but is a good trick to know.

For now I am working on a join with the date table to generate a table owher I will have one row per day in the period and will have to count the rows for a specific month, year etc ... still working on it ...