Curreny Year and Current Month Slicer

[GuestUser]

Hi,

We have power bi reports created using - SSAS (Tabular) -- Connect Live

We need to show dynamic current fiscal year and current fiscal month in slicer

Fiscal Year : April to March

We tried below appraoch :(below works fine for calender date not fiscal date)

1) Create a new column in Date Table

Current Year= IF(YEAR(Table(Date)=YEAR(NOW()),"Current Year", [Year])

2) Create another column

Current_Month = IF(YEAR(Table[DATE])=YEAR(NOW()) && 'Table'[MONTH_OF_YEAR] = Month(Today()),"Current Month",'Table'[Month])

But its not working on fiscal level...so the output is coming incorrect

We do not have fiscal date in date table

We have below column in date table in below format

cal year          cal date                     fy_year                        fy_month

2019           01-MAr-2019          2018-2019                   2018-2019/MAR
2020           01-Apr-2020          2020-2021                 2020-2021/APR

Any suggestions pls on how to achieve current year and month

[amitchandak]

@GuestUser ,

Please find the attached file, that has a calendar from April to March. You can use start Date of the year or end date of the year get your Financial year.

Also, time intelligence function dateytd and totalytd take end parameter. that also you can use.

YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(('Date'[Date]),"3/31"))
This Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD((ENDOFYEAR('Date'[Date])),"3/31"))

Last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-1,Year),"3/31"))
Last YTD complete Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(ENDOFYEAR(dateadd('Date'[Date],-1,Year)),"3/31"))
Last to last YTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESYTD(dateadd('Date'[Date],-2,Year),"3/31"))

Year behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Year))

[smarthp29]

@GuestUser Try using CalculateTable function and getting the fy_year and fy_month column to mark them as the latest. 

May not work, unable to understand the entire implementation with just information for 1 table.

[GuestUser]

The Slicers that will be used will be taken from Date Table only. So only one table will be used 

I basically need formula how to identify fiscal year and mark it as current year

[mwegener · Accepted solution]

Hi @GuestUser ,

check this PBIX

[GuestUser]

Thanks @mwegener  and @amitchandak  for helping!!