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 ,

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

Anonymous's avatar
Anonymous
Not applicable
3 years ago

Thank you for this. If i am correct, this measure just calculates everything that falls within the date of the parameter. The idea is to calculate eveything between a set period that changes every year. Sort of like how the financial year for certain countries begin in April. So say i want to calculate data from April 2022 to March 2023.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    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.