undefined

[Anonymous]

Hi all,

I have column of billing dates, i want to filter out the current financial dates from that column. Current financial year means april 2020 to march 2021.

Please help me out. How i can do it.

Thanks

[amitchandak · Accepted solution]

@Anonymous ,

1. What exact output you want. 

2. Why you do not a date table.

You can try  a new column like this your table 

New Column =
var _year = if(month(today())<=3, year(today())-1, year(today()))
var _st = date(_year,4,1)
var _end = eomonth(_st,11) //if need make 11 as 12
return
if(Table[Date] >= _st && Table[Date] <=_end , "This FY", "Other")

[amitchandak]

@Anonymous , Output is not clear.

You can create a calendar and use that. You use this blog to get the calendar April-March

Creating Financial Calendar - From Any Month
https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441

you can also refer

Power BI — Year on Year with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-ytd-questions-time-intelligence-1-5-e3174b39f38a
https://www.youtube.com/watch?v=km41KfM_0uA
Power BI — Qtr on Qtr with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-qtd-questions-time-intelligence-2-5-d842063da839
https://www.youtube.com/watch?v=8-TlVx7P0A0
Power BI — Month on Month with or Without Time Intelligence
https://medium.com/@amitchandak.1978/power-bi-mtd-questions-time-intelligence-3-5-64b0b4a4090e
https://www.youtube.com/watch?v=6LUBbvcxtKA

[Anonymous]

Hi @amitchandak 

I don't want to create any financial year column but want to filter out dates from the column lying in current financial year.

Thank you.

[amitchandak · Accepted solution]

@Anonymous ,

1. What exact output you want. 

2. Why you do not a date table.

You can try  a new column like this your table 

New Column =
var _year = if(month(today())<=3, year(today())-1, year(today()))
var _st = date(_year,4,1)
var _end = eomonth(_st,11) //if need make 11 as 12
return
if(Table[Date] >= _st && Table[Date] <=_end , "This FY", "Other")

[Anonymous]

Thank you @amitchandak . This solution worked for me. 

Can you explain me that current financial year is taken as 2021 or 2020?

[amitchandak · Accepted solution]

@Anonymous , it is taken from 1-Apr-2020 to 31-Mar-2021

[Anonymous]

@amitchandak Understood. Can you also tell what 11 in eomonth signifies?