Dax Issue

[Tezaroyal]

Hi,

I'm trying to show the total nights between the Arrivaldate and depaturedate based on slicer selection 

Below is an example. 

The logic behind the Total nights column is the count of nights between the date range that we selected in Slicer is

(7/1/2023- 7/13/2023 ) which I created. For example, Customer A arrived on 7/1/2023 and departed on 7/16/2023. We need to count only the nights which he is staying in between the daterange which we selected in slicer and but not the depature date as he is leaving that day and the total nights are = 13

The number of nights is coming from DB by the logic of the number of nights between arrival and departure date, which is 15 for (Arrived on 7/1/2023 and departure on 7/16/2023). Here we are not counting the night of the departure date as he is leaving the hotel.

For Total Night I wrote the measure below. Now I need the sum of total nights on a visual card and but I'm getting blank after draging it into in card . Can someone please help me?  

TotalNights_Final = IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])>[enddate], DATEDIFF([startdate],[enddate],DAY)+1,

    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>=[startdate]&& SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])> [enddate],DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),[enddate],DAY)+1,

    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])< [enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),

    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])<[enddate], DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),

    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<=[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])<[enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),

    IF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])<=[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])=[enddate],DATEDIFF([startdate],SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),

    if(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate])>[startdate]&&SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate])=[enddate],DATEDIFF(SELECTEDVALUE('report vwRevenueDashboardReport_Data'[ArrivalDate]),SELECTEDVALUE('report vwRevenueDashboardReport_Data'[DepartureDate]),DAY),BLANK()

)))))))

[Greg_Deckler]

@Tezaroyal First, please vote for this idea: https://ideas.powerbi.com/ideas/idea/?ideaid=082203f1-594f-4ba7-ac87-bb91096c742e

This looks like a measure totals problem. Very common. See my post about it here: https://community.powerbi.com/t5/DAX-Commands-and-Tips/Dealing-with-Measure-Totals/td-p/63376

Also, this Quick Measure, Measure Totals, The Final Word should get you what you need:
https://community.powerbi.com/t5/Quick-Measures-Gallery/Measure-Totals-The-Final-Word/m-p/547907

Also: https://youtu.be/uXRriTN0cfY
And: https://youtu.be/n4TYhF2ARe8

[rubayatyasmin · Accepted solution]

HI, @Tezaroyal 

You are attempting to create a card visual with this measure, but it is likely returning BLANK because the card visual does not have a row context to reference. This issue arises because a card visual is designed to display single aggregated value (like a sum or an average), and SELECTEDVALUE is not able to work in this context as it returns the value when there's only one value in the specified column for the current filter context, otherwise it returns an alternate result you specify or BLANK() by default.

You may need to revise your measure to handle a situation when multiple or no rows are selected, possibly by replacing SELECTEDVALUE with MIN or MAX, or other suitable aggregate function that would work for your specific case. This would allow you to calculate the total nights even when more than one row is selected.

However, a more effective way could be creating a new table that will create a row for each night a guest stayed and then sum up the rows that fall into the selected slicer dates. This could give you a simple count of rows as a result which could be placed in the card visual.

Remember, without a specific row context, SELECTEDVALUE function will likely not work the way you expect in card visuals.

If my assistance helped you in any way, hit 👍.