Last X-Axis Point Data Label flexible for quarter and Period

[icassiem]

Hi,

I have this datalable for my last point working on period/month only but when the user drills up to quarter, then it does not work

I have this datalabel on the Y-Axis value for budget by month, and this label was to show the variance to total budget on the budget y-axis

LastDataPointLabelTarget =

var DateLast = EOMONTH(calculate(max(Calc_SpecialDates[Date]), filter(allselected(Calc_SpecialDates),Calc_SpecialDates[Period]="FYTD_Full")),0)

var DateSelected = EOMONTH(Sales[Date],0)

var SalesTargetFull = calculate(SUM(Sales[Target]),REMOVEFILTERS(Calc_SpecialDates),Calc_SpecialDates[Period] = "FYTD_Full")

var SalesActualFull = calculate(SUM(Sales[Actual]),REMOVEFILTERS(Calc_SpecialDates),Calc_SpecialDates[Period] = "FYTD_Full")

var Result =

 if(DateLast = DateSelected,

   if(RevenueActualFull=0,0,(SalesActualFull/SalesTargetFull))

 )

Return

  Result

Please help me make this calculation flexible to be able to drill up and down between quarter and period

Regards

[v-jianpeng-msft]

Hi, @icassiem 

You can use isinscope to determine which drill-down level the current user is at, and then execute the corresponding calculation logic. Here is my sample data:

First, I use the following variable to determine whether the current quarter is the largest quarter:

VAR Quater = QUARTER(MAXX(ALL('Table'[Date]),'Table'[Date])) = MAX('Table'[Date].[QuarterNo])

 I use the following command to determine when the user drills down to the quarter, execute the corresponding logic, if not, then execute other logic.
Please note that your date column needs to have a hierarchical structure.

IF(
        ISINSCOPE('Table'[Date].[Quarter]) && Quater, 
        IF(
            SalesActualPeriod = 0, 
            0, 
            SalesActualPeriod / SalesTargetPeriod
        ), 
        Result
    )

The results are as follows:

If you have any questions or need additional support, please let us know. We are happy to continue to assist you.
Thanks for your patience and we look forward to hearing from you.

How to Get Your Question Answered Quickly

If it does not help, please provide more details with your desired output and pbix file without privacy information (or some sample data) .

Best Regards

Jianpeng Li

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

[icassiem]

Thank You both @v-jianpeng-msft  @TomMartens 

@v-jianpeng-msft 

i created the date hierarchy on calc_date dimension but i am battling

Test2 =

var QUATER = QUARTER(MAXX(ALL(Calc_Calendar[Date]),Calc_Calendar[Date])) = MAX(Calc_Calendar[DateFinQuarter])

var DateLast = EOMONTH(calculate(max(Calc_SpecialDates[Date]), filter(allselected(Calc_SpecialDates),Calc_SpecialDates[Period]="FYTD_Full")),0)

var DateSelected = EOMONTH(Sales[Date],0)

var Result =

IF(

    //ISINSCOPE(Calc_Calendar[DateFinQuarter]) && QUATER,

      ISINSCOPE(Calc_Calendar[Date]) && QUATER,            

1

  //   if(DateLast = DateSelected,

   //      1           //if(SalesActualFull=0,0,(SalesActualFull/SalesTargetFull))

    //,2)

,3)

RETURN  Result

the ISINSCOPE seems to always be false as it return the value 3 all the time

Please Help

[v-jianpeng-msft · Accepted solution]

Hi, @icassiem 

I'm currently using the date column, and the default hierarchy is shown in the following image:

You should understand the following dax expression correctly:

QUARTER(MAXX(ALL(Calc_Calendar[Date]),Calc_Calendar[Date])) = MAX(Calc_Calendar[DateFinQuarter])

Here it will be determined whether the quarter of the current maximum date is equal to the quarter of the current axis.

The QUARTER function returns a number. You should adjust this judgment expression according to your actual situation.

If you can provide some data or pbix that doesn't contain sensitive information, and describe the results you expect, it will help me write a suitable DAX expression for you.

Best Regards

Jianpeng Li

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

[icassiem]

Thank you; I battled for hours and needed to move on as I have a deadline and placed a kpi card rather. Thank You

[TomMartens]

Hey @icassiem ,

please create a pbix that contains sample data, but still reflects your semantic model (tables, relationships, calculated columns, and measures). Upload the file to Onedrive, Google Drive, or Dropbox, and share the link.

Do not forget to explain the expected result behavior based on the data you provide.

Regards,

Tom