Lookup for FY and FQ

[Anonymous]

Hello experts
I need some of your opinion on how to handle one issue I am facing.
I have a date table with custom FY and FQ This is connected to a fact table what has over 30 date fields. And user informed me that they would like to be able to know FY / FQ for any of the date fields. I connected one date field with Date table with active relation. And ended up creating a new table with summarize and lookupfunction where I have FY /FQ listed for all date fields.
I know about creating inactive relation b/w fields and userelationship function, but look like it is for calculation only. In my case, user would like to pull FY /FQ for each date field.
I am wondering if there is any way to avoid creating a big table with over 60 columns and do a measure or something else instead?
Thanks for any tips

[amitchandak]

You can use datesytd and totalytd as you can give year-end date there

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

In case qtr are like jan-feb-mar , then you can use datesqtd.

But if they are not, create a qtr rank in your date table (on YYYYQ type column) = Year& Quarter(Date[Date])

Rank Qtr = RANKX(All(Date),Date[Qtr Year],,asc,dense)

Now use this to get prior QTR

Last QTR = CALCULATE(SUM(Sales[Sales Amount]),filter(all(Date),Date[Rank Qtr] = max(Date[Rank Qtr] )-1))

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s.

Refer
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi

https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

Appreciate your Kudos. In case, this is the solution you are looking for, mark it as the Solution.
In case it does not help, please provide additional information and mark me with @
Thanks. My Recent Blogs -Decoding Direct Query - Time Intelligence, Winner Coloring on MAP, HR Analytics, Power BI Working with Non-Standard TimeAnd Comparing Data Across Date Ranges
Connect on Linkedin

[Anonymous]

Thanks,
To explain my question, user did not communicate that they are doing any calculation. But they would like to pull up custom FY and FQ ( from company FY date table) for any of the date fields.
So I did not understand how your Dax are helping to solve.

[amitchandak]

Ideally, you should join your tables with the date of date table and date of your fact tables, then you should be able to group data on any field of your date table.