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

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply

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

@madepassionned  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

@madepassionned  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

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?

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
)

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.

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
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

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

Top Solution Authors