Forum Discussion

Mahipal's avatar
Mahipal
Helper I
4 years ago

How to link Custom Fiscal Calendar table with time attribute?

We have a requirment of using custom time as time and able to filter using custome time without joining with other tables. 

Table 1 - id ,DateOpened,DateClosed, Casecount
1, 03/04/2021,05/04/2021, 2
2, 09/05/2021,10/05/2021, 4
3, 11/08/2021,15/08/2021, 3
Table 2 - FiscalDate, FiscalQtr,FiscalMonth,FiscalYear (Fiscal Calendar Table)
03/04/2021 - Belongs to FiscalQtr 1
09/05/2021 - Belongs to FiscalQtr 1
11/08/2021 - Belongs to FiscalQtr 2
Now we want to create reports based on either of dateopened/ dateclosed
Fiscal Qtr Fiscal Year sum(CaseCount)
1 2021 6
2 2021 3

we donot want to put a join to fiscal calendar table and table 1 with each of date attributes
so how can we link fiscal calendar as time attribute so that it works for each of the date fields ???

 

Dataflow

1 Reply

  • edhans's avatar
    edhans
    Community Champion
    4 years ago

    I'm not seeing a time component, so it doesn't look like you need time anywhere.

    you should create a date table that has all of the attributes you need. The Date field must start at the beginning of your needs and go through the end, and there must be a record, and only one record, for each day. So Jan 1, 2019 through Dec 31, 2022 would be roughly 1,300 records.

    You need to then add a lot of attributes. Month, Month Name, Year, Fiscal Year, Quarter number, quarter name, etc. You then filter by those attributes. The Date field is what should be connected to your Fact table.
    Here is an example of a good date table you can expand on: Creating a Dynamic Date Table in Power Query

     

    If you need more help, be clearer in the expectations of what you are trying to do. thanks!

    How to get good help fast. Help us help you.

    How To Ask A Technical Question If you Really Want An Answer

    How to Get Your Question Answered Quickly - Give us a good and concise explanation
    How to provide sample data in the Power BI Forum - Provide data in a table format per the link, or share an Excel/CSV file via OneDrive, Dropbox, etc.. Provide expected output using a screenshot of Excel or other image. Do not provide a screenshot of the source data. I cannot paste an image into Power BI tables.