How to limit YoY benchmark calculation to the same day of this month

[tanat_inc]

Hi community, 

I use the code below to generate many benchmarks  (Budget, YoY, last 2 yr, last mo, last 2 mo).

It's work well when test with full month data.

Benchmark =

  SWITCH( SELECTEDVALUE('zz_compare_dropdown'[compare_id],1),

     1, '02_m_budget'[Target Selected],

     2, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,YEAR)),

     3, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,YEAR)),

     4, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,MONTH)),

     5, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,MONTH)),

     6, BLANK(),

     BLANK()

)

However when apply to the current month (on April 13) , it calculate the sum of whole 31 days of last April, not just 13 days. How should I modify it to calculate sum of the equal day to the day pass in current month.

[Jihwan_Kim]

Hi, @tanat_inc 

Please correct me if I wrongly understood your question.

Please try to change measures something like below in order show it only until the date that has the display value.

=>>>

IF (
ISBLANK ( [Display Value] ),
BLANK (),
CALCULATE (
'02_m_budget'[Display Value],
DATEADD ( 'd_date_table'[Date], -1, MONTH )
)
)

Hi, My name is Jihwan Kim.

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

Linkedin: https://www.linkedin.com/in/jihwankim1975/

[tanat_inc]

I have tried to add ISBLANK as the following code, but when I choose to display YoY, Benchmark still keep calculate total 31 days of 2020 April.

Benchmark =

  SWITCH( SELECTEDVALUE('zz_compare_dropdown'[compare_id],1),

    1, '02_m_budget'[Target Selected],

    2, IF(ISBLANK('02_m_budget'[Display Value]), BLANK(), CALCULATE('02_m_budget'[Display Value],
        DATEADD('d_date_table'[Date],-1,YEAR))),

    3, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,YEAR)),

    4, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,MONTH)),

    5, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,MONTH)),

    6, BLANK(),

    BLANK()

  )

It doesn't have this issue when plot both of them in line and clustered chart, but the issue is when I put Benchmark in display card. 

Thanks for your help, and looking forward for further suggestion.

[Jihwan_Kim · Accepted solution]

Hi,

what I meant was something like below.

sorry for not making clear.

please try the below. Or, please share your sample pbix file's link here, then I can try to look into it to come up with a more accurate measure.

Benchmark =

  SWITCH( SELECTEDVALUE('zz_compare_dropdown'[compare_id],1),

    1, '02_m_budget'[Target Selected],

    2, IF(ISBLANK('02_m_budget'[Display Value]), BLANK(), CALCULATE('02_m_budget'[Display Value],
        DATEADD('d_date_table'[Date],-1,YEAR))),

    3, IF(ISBLANK('02_m_budget'[Display Value]), BLANK(),CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,YEAR))),

    4, IF(ISBLANK('02_m_budget'[Display Value]), BLANK(),CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,MONTH))),

    5, IF(ISBLANK('02_m_budget'[Display Value]), BLANK(),CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,MONTH))),

    6, BLANK(),

    BLANK()

  )

[tanat_inc]

Sorry for long time missing, was sick.

When I test the code you provided in simple model, it's work. However it does not work well in my model, may be because '02_m_budget'[Display Value]  is a measure that already contain filter itself and it also reference to another measure as below : 

Display Value =

SWITCH(SELECTEDVALUE('zz_unit_dropdown'[unit_id], 1),

1, '02_m_budget'[Visit Display],

2, '02_m_budget'[Revenue Display],

BLANK()

)

Revenue Display =

SWITCH(SELECTEDVALUE('zz_target_dropdown'[target_code],1),

1, '00_m_revenue'[Total Revenue],

2, '00_m_revenue'[New Patient Revenue],

3, '00_m_revenue'[IPD Revenue],

4, '00_m_revenue'[OPD Revenue],

5, '00_m_revenue'[Thai Insurance Revenue],

6, '00_m_revenue'[Refer In Revenue],

7, '00_m_revenue'[Inter Insurance Revenue],

BLANK()

)

Total Revenue =

CALCULATE(

SUM('f_bill_info'[net_amt]),

USERELATIONSHIP(d_date_table[Date],f_bill_info[bill_date])

)

and so on....

I have already marked it as solution, since it work in general situation and I have also able to get it work from your suggested idea, but the code seems to be very ineffective. If you could suggest cleaner and more effective way to implement the code below, it will be very appreciated.

Benchmark to date =

VAR today_day = DAY(MAX(d_date_table[Date]))

VAR today_month = MONTH(MAX(d_date_table[Date]))

VAR today_year = YEAR(MAX(d_date_table[Date]))

VAR last_date_1_ = DATE(today_year-1,today_month,today_day)

VAR last_date_2_ = DATE(today_year-2,today_month,today_day)

VAR last_date_3_ = IF( '04_m_drill'[is_full_month_], LASTDATE(DATEADD(d_date_table[Date],-1,MONTH)),

SWITCH(today_month,

1, DATE(today_year-1,12,today_day),

DATE(today_year,today_month-1,today_day)

)

)

VAR last_date_4_ = IF( '04_m_drill'[is_full_month_], LASTDATE(DATEADD(d_date_table[Date],-2,MONTH)),

SWITCH(today_month,

1, DATE(today_year-1,11,today_day),

2, DATE(today_year-1,12,today_day),

DATE(today_year,today_month-2,today_day)

)

)

RETURN

SWITCH( SELECTEDVALUE('zz_compare_dropdown'[compare_id],6),

1, '02_m_budget'[Target Selected],

2, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,YEAR), d_date_table[Date]<=last_date_1_),

3, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,YEAR), d_date_table[Date]<=last_date_2_),

4, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,MONTH), d_date_table[Date]<=last_date_3_),

5, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,MONTH), d_date_table[Date]<=last_date_4_),

6, BLANK(),

BLANK()

)

[tanat_inc]

Sorry for long time missing, was sick.

I don't know why but that way work well in my testing file but does not work well for my project. May be because of '02_m_budget'[Display Value]  is multiple hierachy measure that contain many filter inside.

Since it work well and look clean, I've already mark it as solution for other to search.

However I got it to work by the idea suggested by you, but the code seems to be messed. Could you suggested the simpler way to get the same result ?

Benchmark to date =

VAR today_day = DAY(MAX(d_date_table[Date]))

VAR today_month = MONTH(MAX(d_date_table[Date]))

VAR today_year = YEAR(MAX(d_date_table[Date]))

VAR last_date_1_ = DATE(today_year-1,today_month,today_day)

VAR last_date_2_ = DATE(today_year-2,today_month,today_day)

VAR last_date_3_ = IF( '04_m_drill'[is_full_month_], LASTDATE(DATEADD(d_date_table[Date],-1,MONTH)),

SWITCH(today_month,

1, DATE(today_year-1,12,today_day),

DATE(today_year,today_month-1,today_day)

)

)

VAR last_date_4_ = IF( '04_m_drill'[is_full_month_], LASTDATE(DATEADD(d_date_table[Date],-2,MONTH)),

SWITCH(today_month,

1, DATE(today_year-1,11,today_day),

2, DATE(today_year-1,12,today_day),

DATE(today_year,today_month-2,today_day)

)

)

RETURN

SWITCH( SELECTEDVALUE('zz_compare_dropdown'[compare_id],6),

1, '02_m_budget'[Target Selected],

2, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,YEAR), d_date_table[Date]<=last_date_1_),

3, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,YEAR), d_date_table[Date]<=last_date_2_),

4, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-1,MONTH), d_date_table[Date]<=last_date_3_),

5, CALCULATE('02_m_budget'[Display Value], DATEADD('d_date_table'[Date],-2,MONTH), d_date_table[Date]<=last_date_4_),

6, BLANK(),

BLANK()

)

[tanat_inc]

Yes, I just tried to test on option 2 only before move on. Your first message is already clear, just that I want to test first.  Unfortunately it does not work.

I put [Display Value] and [Benchmark] together in Line and clustered chart below, and it's work well (It doen't extended my x-axis to Arpil 30)

However when I put [Benchmark] on display card next to the chart, it calculate from 1/4/2020 - 30/4/2020, instead of 1/4/2020 - 12/4/2020.



I will try to modify the data before sending you pbix, but it should take some times.

Thanks for your kindness.