Problem with measure limits

gumis_rulez — Fri, 27 Sep 2024 07:36:09 GMT

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

Re: Problem with measure limits

Selva-Salimi — Fri, 27 Sep 2024 12:23:29 GMT

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.

topic Re: Problem with measure limits in DAX Commands and Tips

Problem with measure limits

Re: Problem with measure limits