Forum Discussion

Peter2023's avatar
Peter2023
Icon for Helper I rankHelper I
1 year ago
Solved

Calculate Actual booking

Hello, Please help me resolve the following issue. I want to calculate the monthly actual booking using the formula: Actual Booking the current month = Actual Booking of the previous month + Bookin...
Need Help
  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Peter2023 

    First, change the type of "Monthnumber"



    Then try the following measure:

    Measure = 
VAR _month = SELECTEDVALUE('Date Lib'[Monthnumber])
VAR _current_booking = [Booking]
VAR _current_cancel = [Cancel]
VAR _current_retails = [Retails]
VAR _previous_booking = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Booking])
VAR _previous_cancel = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Cancel])
VAR _previous_retails = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Retails])

RETURN
_current_booking + _previous_booking -_current_cancel - _current_retails -_previous_cancel - _previous_retails

     

    Result:

     

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Anonymous's avatar
    Anonymous
    1 year ago

    Hi Peter2023 

    First, use the following dax to get the week number:

    weeknum = WEEKNUM('Date lib'[Date])

     

    Then try the following Measure:

    Actual booking = 
VAR _month = SELECTEDVALUE('Date lib'[Monthnumber])
VAR _week = SELECTEDVALUE('Date lib'[weeknum])
VAR _current_booking = [Booking]
VAR _current_cancel = [Cancel]
VAR _current_retails = [Retails]
VAR _previous_booking_month = SUMX(FILTER(ALL('Date lib'),'Date lib'[Monthnumber] <= _month-1),[Booking])
VAR _previous_cancel_month = SUMX(FILTER(ALL('Date lib'),'Date lib'[Monthnumber] <= _month-1),[Cancel])
VAR _previous_retails_month = SUMX(FILTER(ALL('Date lib'),'Date lib'[Monthnumber] <= _month-1),[Retails])
VAR _previous_booking_week = SUMX(FILTER(ALL('Date lib'),'Date lib'[weeknum] <= _week-1),[Booking])
VAR _previous_cancel_week = SUMX(FILTER(ALL('Date lib'),'Date lib'[weeknum] <= _week-1),[Cancel])
VAR _previous_retails_week = SUMX(FILTER(ALL('Date lib'),'Date lib'[weeknum] <= _week-1),[Retails])

RETURN 
IF(ISINSCOPE('Date lib'[weeknum]),_current_booking + _previous_booking_week -_current_cancel - _current_retails -_previous_cancel_week - _previous_retails_week,_current_booking + _previous_booking_month -_current_cancel - _current_retails -_previous_cancel_month - _previous_retails_month)

     

    Result:

     

     

     

    Best Regards,

    Jayleny

     

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

Anonymous's avatar
Anonymous
Not applicable
1 year ago

Hi Peter2023 

First, change the type of "Monthnumber"



Then try the following measure:

Measure = 
VAR _month = SELECTEDVALUE('Date Lib'[Monthnumber])
VAR _current_booking = [Booking]
VAR _current_cancel = [Cancel]
VAR _current_retails = [Retails]
VAR _previous_booking = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Booking])
VAR _previous_cancel = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Cancel])
VAR _previous_retails = SUMX(FILTER(ALL('Date Lib'),'Date Lib'[Monthnumber] <= _month-1),[Retails])

RETURN
_current_booking + _previous_booking -_current_cancel - _current_retails -_previous_cancel - _previous_retails

 

Result:

 

 

 

 

Best Regards,

Jayleny

 

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

  • Peter2023's avatar
    Peter2023
    Icon for Helper I rankHelper I
    1 year ago

    Thank you, you make my day!

  • Peter2023's avatar
    Peter2023
    Icon for Helper I rankHelper I
    1 year ago

    Anonymous Sorry if I disturb you again. But how to use the measure with a day or week number? The result is incorrect after I add it. .

     

    After add week 

     

    Date library code with week number and week range:

    Spoiler
    Date lib =
    VAR minDate = MIN(booking[bookingdate])
    VAR maxDate = MAX(booking[bookingdate]
    )
    RETURN
    ADDCOLUMNS(
        CALENDAR(DATE(YEAR(minDate), 1, 1), DATE(YEAR(maxDate), 12, 31)),
        "DateAsInteger", FORMAT([Date], "YYYYMMDD"),
        "Year", YEAR([Date]),
        "No", VALUE([Date]),
        "Monthnumber", FORMAT([Date], "MM"),
        "YearMonthnumber", FORMAT([Date], "YYYY/MM"),
        "YearMonthShort", FORMAT([Date], "YYYY/mmm"),
        "MonthNameShort", FORMAT([Date], "mmm"),
        "MonthNameLong", FORMAT([Date], "mmmm"),
        "DayOfWeekNumber", WEEKDAY([Date]),
        "DayOfWeek", FORMAT([Date], "dddd"),
        "DayOfWeekShort", FORMAT([Date], "ddd"),
        "Quarter", "Q" & FORMAT([Date], "Q"),
        "YearQuarter", FORMAT([Date], "YYYY") & "/Q" & FORMAT([Date], "Q"),
       
        "WeekInMonth",
            VAR DayOfMonth = DAY([Date])
            RETURN
                SWITCH(
                    TRUE(),
                    DayOfMonth <= 7, 1,
                    DayOfMonth <= 14, 2,
                    DayOfMonth <= 21, 3,
                    4
                ),
       
        "WeekRange",
            VAR DayOfMonth = DAY([Date])
            VAR WeekNum = SWITCH(
                TRUE(),
                DayOfMonth <= 7, 1,
                DayOfMonth <= 14, 2,
                DayOfMonth <= 21, 3,
                4
            )
            VAR FirstDayOfMonth = DATE(YEAR([Date]), MONTH([Date]), 1)
            VAR LastDayOfMonth = EOMONTH([Date], 0)
            VAR WeekStartDate = FirstDayOfMonth + (WeekNum - 1) * 7
            VAR WeekEndDate =
                IF(
                    WeekNum = 4,
                    LastDayOfMonth,
                    WeekStartDate + 6
                )
            RETURN FORMAT(WeekStartDate, "dd/MM") & " - " & FORMAT(WeekEndDate, "dd/MM")
    )

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      1 year ago

      Hi Peter2023 

      Please provide sample data that fully covers your issue and the expected outcome based on the sample data you provided. Please remove any sensitive data in advance.

       

       

       

       

      Best Regards,

      Jayleny

       

      If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.