Dynamic date period

[Anonymous]

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 sure how to make my date to cover different years. 

what i have is:

IF('Appraisal Date'[Date] > 01/06/2022, 1, 0) 

Is it possible to have 01/06/2022 change dynamically for each appraisal year?

[Arul]

@Anonymous ,

Do you have that date anywhere in your data model?

Thanks,

Arul

[Anonymous]

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

[Arul]

@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]

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]

@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]

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 · Accepted solution]

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.