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

Power BI is turning 10! Let’s celebrate together with dataviz contests, interactive sessions, and giveaways. Register now.

Reply
manup07
Frequent Visitor

DAX to get number of nights per month between 2 dates


CONTEXT:

manup07_0-1704940316932.png

I have a table with different room transactions(reservations), I'm trying to calculate the number of nights between two dates so I can usem them in a pivot table or any graph, like this:

manup07_2-1704940607308.png

 

This result is not correct because I should have in december(diciembre) 8 instead of 9 and that 1 remaning should go to january, and so on. 

QUESTION:

How to create a DAX or how to solve this issue.

1 ACCEPTED SOLUTION
Jihwan_Kim
Super User
Super User

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

 

Jihwan_Kim_1-1704946690065.png

 

 

Jihwan_Kim_0-1704946671093.png

 

 

expected result measure: =
VAR _currentmonth =
    MAX ( 'Calendar'[Year-Month sort] )
VAR _t =
    GENERATE (
        Data,
        FILTER (
            'Calendar',
            'Calendar'[Date] > Data[Start]
                && 'Calendar'[Date] <= Data[End]
        )
    )
RETURN
    COUNTROWS ( FILTER ( _t, 'Calendar'[Year-Month sort] = _currentmonth ) )

 


 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     

   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     

   


 


View solution in original post

4 REPLIES 4
Jihwan_Kim
Super User
Super User

Hi, I am not sure how your semantic model looks like, but I tried to create a sample pbix file like below.

Please check the below picture and the attached pbix file whether it suits your requirement.

 

Jihwan_Kim_1-1704946690065.png

 

 

Jihwan_Kim_0-1704946671093.png

 

 

expected result measure: =
VAR _currentmonth =
    MAX ( 'Calendar'[Year-Month sort] )
VAR _t =
    GENERATE (
        Data,
        FILTER (
            'Calendar',
            'Calendar'[Date] > Data[Start]
                && 'Calendar'[Date] <= Data[End]
        )
    )
RETURN
    COUNTROWS ( FILTER ( _t, 'Calendar'[Year-Month sort] = _currentmonth ) )

 


 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     

   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     

   


 


I think it works, but before I marked it as a solution, I need to know if this will work with other visuals, for example a line chart, or to create other measures with it

Hi, thank you for your message, and please provide your sample pbix file and then I can try to look into it.

Thanks.


 

    Microsoft MVP
 

 

   


      If this post helps, then please consider accepting it as the solution to help other members find it faster, and give a big thumbs up.


   


     
        LinkedInVisit my LinkedIn page
     

   


   


     
        Outlook BookingSchedule a short Teams meeting to discuss your question

     

   


 


No worries, your answer work wonders, thank you so much @Jihwan_Kim 

Helpful resources

Announcements
June 2025 Power BI Update Carousel

Power BI Monthly Update - June 2025

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

May 2025 Monthly Update

Fabric Community Update - May 2025

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