Calendar for Rolling 12 months - A new column

[Anonymous]

Hey,

I would want a Flag column that I am creating to look like this:

Flag   Year     Month  Day

0        2017    Jan        15

0        2017    Jan         16

.

.

.

1       2017    July        1

1       2017    July         2

.

.

.

1      2018      July         31

 Flag here is 1 for July because we are in the next month August. At the beginning of the August, Flag needs to be set to 1 for all the dates thar are in between July 2017 to July 2018.

Similarly, once we go to September 2018, Flag should be 1 from August 1, 2017 - August 31, 2018

The QlikView equivalent would be : inMonths(12, DATE, Today(), -1, num(month(Today()))). I need it's equivalent in Power BI.

Thank You!

[Anonymous · Accepted solution]

@Anonymous here you go.

Rolling12FlagM = 
VAR MaxDateV = CALCULATE(MAX(Query1[CalendarDate]),FILTER(ALL(Query1),Query1[CalendarDate]=MAX(Query1[CalendarDate])))
VAR CalDateMaxDateDiff = DATEDIFF(MaxDateV,Query1[CalendarDate],MONTH)
Return SWITCH(TRUE(),
              CalDateMaxDateDiff>=-13 && CalDateMaxDateDiff <= -1,"Yes",
              "No"
)   

[Anonymous · Accepted solution]

 Hey, this gives a no for everything. But I want a yes for all dates between July 2017- July 2018

[Anonymous · Accepted solution]

@Anonymous here you go.

Rolling12FlagM = 
VAR MaxDateV = CALCULATE(MAX(Query1[CalendarDate]),FILTER(ALL(Query1),Query1[CalendarDate]=MAX(Query1[CalendarDate])))
VAR CalDateMaxDateDiff = DATEDIFF(MaxDateV,Query1[CalendarDate],MONTH)
Return SWITCH(TRUE(),
              CalDateMaxDateDiff>=-13 && CalDateMaxDateDiff <= -1,"Yes",
              "No"
)   

[Anonymous · Accepted solution]

 Hey, this gives a no for everything. But I want a yes for all dates between July 2017- July 2018

[Anonymous · Accepted solution]

@Anonymous I tested the formula and it worked correctly for me. Can you post your PBIX file? Also can you check what is the max date value.

MaxDate = 
VAR MaxDateV = CALCULATE(MAX(Query1[CalendarDate]),FILTER(ALL(Query1),Query1[CalendarDate]=MAX(Query1[CalendarDate])))
Return MaxDateV

[Anonymous]

Hey this worked. But i Just changed 13 to 12 and 1 to 0. Then it worked for me.

Thank You!