Forum Discussion

gumis_rulez's avatar
gumis_rulez
Helper I
1 year ago
Solved

Problem with measure limits

I want to have a measure which calculates Checkouts YTD - and this works well; and second measure which calculates Checkouts LYTD till the date I have data in fct table.

 

Checkouts = 
CALCULATE(
    COUNT(
        'fct_Hotel Revenue'[Reservation Status Date]
    ),
    'fct_Hotel Revenue'[Reservation Status Key] = 2
)

 

Checkouts YTD = 
VAR _Max = MAX('fct_Hotel Revenue'[Reservation Status Date])

RETURN
CALCULATE(
    [Checkouts],
    DATESYTD(dim_Calendar[Date]),
    dim_Calendar[Date] <= _Max
)

 

Checkouts LYTD = 
VAR _Max = MAX('fct_Hotel Revenue'[Reservation Status Date])

RETURN
CALCULATE(
    [Checkouts YTD],
    SAMEPERIODLASTYEAR(dim_Calendar[Date]),
    dim_Calendar[Date] <= _Max
)

 

The problem is with Checkouts LYTD:

First issue:

For table1 total should be 34480.

For table2 both numbers should be 34480.

 

Second issue:

Secondly the measure should be limited till 14th September. And Checkouts LYTD seems to be taking whole month instead of max calendar from;
For 2018 sales till August: 28146. Sales till 14th September: 2711. All together 30 857. In all above the number should 30 857 instead of 34 480.

 

What am I doing wrong?

 

https://drive.google.com/file/d/1_lyGKrXi-Kpn7AZkk8iu_jfQYOB_N2VC/view?usp=sharing 

 

Pawel

  • Hi gumis_rulez 

     

    you should update your LYTD measure as follows:

     

    Checkouts LYTD =
    VAR _Max = MAX('fct_Hotel Revenue'[Reservation Status Date])
    VAR LastSaleDatePY = EDATE ( _max, -12 )

    RETURN
    CALCULATE(
        [Checkouts YTD]
        ,SAMEPERIODLASTYEAR(dim_Calendar[Date]),
        dim_Calendar[Date] <= LastSaleDatePY
    )
     
    If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly. 

1 Reply

  • Hi gumis_rulez 

     

    you should update your LYTD measure as follows:

     

    Checkouts LYTD =
    VAR _Max = MAX('fct_Hotel Revenue'[Reservation Status Date])
    VAR LastSaleDatePY = EDATE ( _max, -12 )

    RETURN
    CALCULATE(
        [Checkouts YTD]
        ,SAMEPERIODLASTYEAR(dim_Calendar[Date]),
        dim_Calendar[Date] <= LastSaleDatePY
    )
     
    If this post helps, then I would appreciate a thumbs up 👍 and mark it as the solution to help the other members find it more quickly.