Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Enhance your career with this limited time 50% discount on Fabric and Power BI exams. Ends August 31st. Request your voucher.

Reply
Anonymous
Not applicable

Calculate days in between two dates for each year

Hi there, 

 

I would like to know how you calculate days in between two dates for each year. So you could make a diagram like this :

 

2.PNG

 

2 ACCEPTED SOLUTIONS
smpa01
Super User
Super User

@Anonymous  you can write a meaure like this

Measure =
VAR _fact =
    FILTER (
        GENERATE ( tbl, DATESBETWEEN ( 'Calendar'[Date], tbl[Begin], tbl[End] ) ),
        [Date] <> tbl[Begin]
            && [Date] <> tbl[End]
    )
VAR _calendar =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

 

smpa01_0-1641226389928.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

View solution in original post

Using GENERATE on a fact table seems like a bad idea from a performance perspective. Your _fact variable could potentially be quite an enormous table.

 

I'd recommend something simpler like this:

DATEDIFF (
    MAX ( MIN ( tbl[Begin] ), MIN ( 'Calendar'[Date] ) ),
    MIN ( MAX ( tbl[End]   ), MAX ( 'Calendar'[Date] ) ),
    DAY
)

View solution in original post

8 REPLIES 8
smpa01
Super User
Super User

@Anonymous  you can write a meaure like this

Measure =
VAR _fact =
    FILTER (
        GENERATE ( tbl, DATESBETWEEN ( 'Calendar'[Date], tbl[Begin], tbl[End] ) ),
        [Date] <> tbl[Begin]
            && [Date] <> tbl[End]
    )
VAR _calendar =
    ADDCOLUMNS (
        'Calendar',
        "count", COUNTX ( FILTER ( _fact, [Date] = EARLIER ( 'Calendar'[Date] ) ), [Date] )
    )
RETURN
    SUMX ( _calendar, [count] )

 

smpa01_0-1641226389928.png

 

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs
Anonymous
Not applicable

MicrosoftTeams-image (114).png

 

The measure is perfect in a diagram but when a switch the same measure in a table or matrix an error occurred:

 

 

MicrosoftTeams-image (113).pngMicrosoftTeams-image (115).png

Can anyone help me with this issue?

Anonymous
Not applicable

Thank you @smpa01

Using GENERATE on a fact table seems like a bad idea from a performance perspective. Your _fact variable could potentially be quite an enormous table.

 

I'd recommend something simpler like this:

DATEDIFF (
    MAX ( MIN ( tbl[Begin] ), MIN ( 'Calendar'[Date] ) ),
    MIN ( MAX ( tbl[End]   ), MAX ( 'Calendar'[Date] ) ),
    DAY
)
Anonymous
Not applicable

MicrosoftTeams-image (114).png

 

The measure is perfect in a diagram but when a switch the same measure in a table or matrix an error occurred:

 

 

MicrosoftTeams-image (113).pngMicrosoftTeams-image (115).png

Can anyone help me with this issue?

Please post the full DAX code you're using for this measure. I can't think of a way the DAX I suggested would give this error, so I'm assuming there are some extra bits.

Anonymous
Not applicable

Thank you @AlexisOlson 😁

👏

Did I answer your question? Mark my post as a solution!
Proud to be a Super User!
My custom visualization projects
Plotting Live Sound: Viz1
Beautiful News:Viz1, Viz2, Viz3
Visual Capitalist: Working Hrs

Helpful resources

Announcements
July 2025 community update carousel

Fabric Community Update - July 2025

Find out what's new and trending in the Fabric community.

July PBI25 Carousel

Power BI Monthly Update - July 2025

Check out the July 2025 Power BI update to learn about new features.