Date tally with measure

[char23]

Hello, 

I am trying to create a visual similar to the one below. I have a date table (Table 1) that is based on min start and max finish dates from another table (Table 2). The date table has a date column, then 3 other columns that show the year, month, and date as first of month. I have another table (Table 2) that is not connected to the date table. It has multiple rows with a column for start date and a column for finish date. I want to write a measure that will output a "1" for every day between the start and finish date (but I want this to be show in the matrix by month). I also want the totals for each month to sum correctly in each column. 

Thank you for the help!

[Anonymous]

Hi @char23 ,

Did the above suggestions help with your scenario? if that is the case, you can consider Kudo or Accept the helpful suggestions to help others who faced similar requirements.

If these also don't help, please share more detailed information and description to help us clarify your scenario to test.

How to Get Your Question Answered Quickly 

Regards,

Xiaoxin Sheng

[danextian · Accepted solution]

Hi @char23 

It is correct that you will need to use a disconnected table (dates has no relationship with fact). Here's a sample measure:

Days in Calendar = 
SUMX ( 
    // Iterate over each date in the Dates table
    Dates, 
    
    // Count how many IDs are active on the current date
    COUNTROWS (
        FILTER ( 
            Table2, 
            // A ID is active if the start date is on or before the current date
            // AND the end date is on or after the current date
            Table2[Date Start] <= Dates[Date]
                && Table2[Date End] >= Dates[Date]
        )
    )
)

Please see the attached pbix for the details.

[Abhilash_P]

Hi @char23 ,
Can you try creating below measures

1. Create a Measure to Flag Dates Between Start and Finish
Active_Days =
VAR SelectedDate = SELECTEDVALUE('Table 1'[Date])
RETURN
SUMX(
'Table 2',
IF(
SelectedDate >= 'Table 2'[Start Date] && SelectedDate <= 'Table 2'[Finish Date],
1,
0
)
)


2. Adjust the Measure to Work at the Month Level
Active_Days_By_Month =
SUMX(
'Table 2',
CALCULATE(
COUNTROWS('Table 1'),
'Table 1'[Date] >= 'Table 2'[Start Date] &&
'Table 1'[Date] <= 'Table 2'[Finish Date]
)
)

[SamanthaPuaXY]

Hi @char23 , you could try a calculated column

NewCol = 
var maxdate = MAX('Table1'[Date])

var mindate = MIN('Table1'[Date])

Return IF('Table2'[Date] < maxdate && 'Table2'[Date] > mindate, 1, blank())