How to make max_date variable in query editator.

[panipat1990]

Dear Team,

i am new in ppower bi.I stuck a requirement. i used another bi tool.Where i made variable in backend.

And used Below Written condition=

Where Date=Date(Floor('$(vMax_Date)'),'DD/MM/YYYY')
Or
Date=Date(Floor('$(vMax_Date)'-1),'DD/MM/YYYY')
Or
Date=Date(Floor(WeekStart('$(vMax_Date)')),'DD/MM/YYYY')
Or
Date=Date(Floor(WeekStart('$(vMax_Date)',-1)),'DD/MM/YYYY')
Or
Date=Date(Floor(MonthEnd('$(vMax_Date)',-1)),'DD/MM/YYYY')
Or
Date=Date(Floor(MonthEnd('$(vMax_Date)',-2)),'DD/MM/YYYY')
Or
Date=Date(Floor(MonthEnd('$(vMax_Date)',-3)),'DD/MM/YYYY')
Or
Date=Date(Floor(MonthEnd(AddYears('$(vMax_Date)',-1))),'DD/MM/YYYY')
Or
Date=Date(Floor(YearStart('$(vMax_Date)',0,4)-1),'DD/MM/YYYY')
Or
Date=Date(Floor(YearStart(AddYears('$(vMax_Date)',-1),0,4)-1),'DD/MM/YYYY');

I Implemented same in power bi..But i am not able to make this.How i can..Kindly help me.

Would be appricate

[v-yulgu-msft]

Hi @panipat1990,

What is your desired result with above formula? Could you please illustrate your requirement with sample data? Besides, maybe you could try Query Parameter in Power BI.

Regards,

Yuliana Gu

[panipat1990]

I want to filter data from max date to last 4 month data..

[panipat1990]

Suppose in my table Date From 01-04-2016 to 25-05-2018

i Want to pickup only Max_date to before 4 month ago not current date..Only Max date exixt in table.

For this max date i made a variable  Max_Date=List.Max(Table[Sale])  Which is 25-05-2018

I created same 4 month..But i want to filter data in above two variable how 

[panipat1990]

This type of error is coming

Expression.Error: A cyclic reference was encountered during evaluation.

[panipat1990]

SO NO ONE KOWN HOW TO MAKE DATE FILTER FROM MAX DATE TO BEFORE MAX DATE TO 4 MONTH EGO

[panipat1990]

Dear Team,

i made a variable of max date..When i filtered data in power editator then it create a error.Why this coming error..

i asked same question many times but noone didnot give me proper answer

[Hardik]

Hi @panipat1990,

Not sure of the error you are getting, but you can use the below method to get the latest date data:

Create a new column in your table and then filter data using that column value.

[panipat1990]

Hi 

I am not asking this.this is frontend funcationality..i asked Power Query for max date.

i made a variable in power editator for max date..but this variable is not working for filtering data...

[Sibrulotte]

Poor panipat!

no one came to the rescue and I have the same issue. Your Qlikview skills are not transferable to Power query easily my friend...

I have the same question, how do you obtain the latest date in a series of values, and use that in as a parameter.

For example, I'd want to filter out just the end of month dates, plus the latest date even if it is not the end of month.

I created a column that has the end of month value, and I'm creating another conditionnal column that tests if these values are the same. But for the latest date that isn't an end of month, I want to know if it's the latest date in my series so I keep it.

Any super bright ideas for that?

[KDTV]

Hello,

The same problem here. I would like to switch from Qlikview to Power BI but before I do this I need to find a solution to filter a table on two dates. In Qlikview I created a variable vKeyDate and if I select that variable via a calender widget it selects all row in the table where posting_date <= vKeyDate and payment_date > vKeydate. How can I do that in Power BI?

Now I have to work with 2 filters in the front-end which is visually not sexy/dummy proof.

Any help would be appreciated here.

[panipat1990]

So no one know how to make date parameter