Forum Discussion

Tlotly's avatar
Tlotly
Icon for Helper V rankHelper V
5 years ago
Solved

Measure to return only current year values on a chart

Good day

Please help. I have a conditional measure  where I need to return only current  fin year values on the visual if there is nothing selected on the Fiscal Year slicer. The dates I'm working with are fin dates i.e. July to June. With help from the forum, I managed to create a measure returning current fin year but I'm struggling to use the same measure in an if statement. 

 

Here is the measure below and if I return just variables I get correct values but when I return the if statement I get values for all the fin years:

      Measure 3 = VAR vCurrentFiscalYear = CALCULATE ( MAX ( 'Calendar'[Fiscal Year] ), year('Calendar'[Date] = TODAY ()))
                      VAR vScoring =
                                   IF (
                                  ISFILTERED ('Calendar'[Fiscal Year]),
                                   [WeightedErrorsCY],
                                 CALCULATE ( [WeightedErrorsCY], 'Calendar'[Fiscal Year] = vCurrentFiscalYear )
                                      )

                  RETURN
                         If(vScoring < 1 ,"Exceptional",
                          If((vScoring >= 5.25 && vScoring <= 11.75) , "Off Track",
                            If((vScoring> 1 && vScoring < 5.25) , "On Track",
                             If(vScoring >1 , "Disciplinary Action"
))
))
 
 
So basically when I haven't selected  a year, I need to see the results as below:

 

Need Help
  • BA_Pete's avatar
    BA_Pete
    5 years ago

    Hi Tlotly ,

     

    Silly question, but is there a reason why you aren't using your original measures and just applying a visual-level filter to your table to exclude blank values of your measures?

     

    I'vejust checked your PBIX, and applied a visual-level filter to your table of [WeightedErrorsCY] is not blank, and I get the following ouput:

     

    This is what you want, right?

     

    Pete

4 Replies

  • BA_Pete's avatar
    BA_Pete
    Icon for Super User rankSuper User
    5 years ago

    Hi Tlotly ,

     

    Add a clause into your measure for what to return if nothing is filtered on the slicer, along the lines of:

    IF(
  NOT ISFILTERED(calendar[Fiscal Year]),
  [CY Measures],
  ...other possible inputs/outputs
)

     

    Pete

    • Tlotly's avatar
      Tlotly
      Icon for Helper V rankHelper V
      5 years ago

      BA_Pete thank you and I get what you mean. I've tried doing that, actual problem is on the [CY Measure] part. I'm struggling to replace [CY Measure] with the condition in order to execute only for the current year. 

       

      If(vScoring < 10 ,"Exceptional",
      If((vScoring >= 50.25 && vScoring <= 119.00) , "Off Track",
      If((vScoring> 10 && vScoring < 50.25) , "On Track",
      If(vScoring >120 , "Disciplinary Action"
      ))
      ))
      • BA_Pete's avatar
        BA_Pete
        Icon for Super User rankSuper User
        5 years ago

        Hi Tlotly ,

         

        Silly question, but is there a reason why you aren't using your original measures and just applying a visual-level filter to your table to exclude blank values of your measures?

         

        I'vejust checked your PBIX, and applied a visual-level filter to your table of [WeightedErrorsCY] is not blank, and I get the following ouput:

         

        This is what you want, right?

         

        Pete