Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Dynamic date period

I am trying to check if an appraisal was completed withing the appraisal period. This should be dynamic as appraisal period changes for each year. I think an IF statement would be fine but i am not s...
General Comment
Interesting Links
Need Help
Tips and Tricks
  • Ashish_Mathur's avatar
    Ashish_Mathur
    3 years ago

    Hi,

    Create a Calendar Table and write these calculated column formulas

    Month name = format(calendar[Date],"mmmm")

    Month number = month(calendar[date])

    Appraisal year = if(calendar[month number]<=6,year(calendar[date])-1&" "&year(calendar[date]),year(calendar[date])&" "&year(calendar[date])+1)

    Hope this helps.

Arul's avatar
Arul
Super User
3 years ago

Anonymous ,

Do you have that date anywhere in your data model?

Thanks,

Arul

Anonymous's avatar
Anonymous
Not applicable
3 years ago

No I dont. i Just know that is when appraisals for the year start

  • Arul's avatar
    Arul
    Super User
    3 years ago

    Anonymous ,

    So, you don't want to change it everytime in the measure in PBI but you want the date needs to be changed dynamically.

    Thanks,

    Arul

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      Yes. I want the date changed dynamically for every year of appraisal. If possible, i'd like to have a date filter for each appraisal year. 

      • Arul's avatar
        Arul
        Super User
        3 years ago

        Anonymous ,

        Create a parameter like this in Power query editor for dates.

        Enable the data load option for parameter query in power query editor.
        Then, write the below measure by changing the appropriate value based on your requirement.

        Color count = 
VAR _parameter = SELECTEDVALUE('Date Parameter'[Date Parameter])
VAR _result = CALCULATE(
    COUNT('Table'[Color]),
    'Table'[Date] = _parameter)
RETURN _result

        Use parameter value wherever inside the measures to make it dynamically change(Please refer the pbix file to see how I have used date parameter value inside the measures).

        To change the parameter value, you have to open edit parameters under transform data.
        Then change the date values there,

         

        Please refer the attached pbix file.

        Thanks,

        Arul