Power BI Exchange

Please login or click SIGN UP FOR FREE to create your PowerBIUG account to join this user group.
Expand all | Collapse all

Using Both Calendar and Fiscal Year in a Report

  • 1.  Using Both Calendar and Fiscal Year in a Report

    Posted May 14, 2019 05:22 PM
      |   view attached
    Hey,

    I have a report where I want to have the option of viewing the information based on either the Calendar Year or the Fiscal Year. I have attached a file that gives an example as to what I am looking for in terms of a visual, but I am only able to choose the entire page/visual to do Calendar OR Fiscal and I cant switch between the two. I do not want to use bookmarks either, I would prefer to utilize DAX for this.

    ------------------------------
    Andrew Alexander
    ------------------------------

    Attachment(s)

    pbix
    TestFile.pbix   66K 1 version


  • 2.  RE: Using Both Calendar and Fiscal Year in a Report

    Top Contributor
    Posted May 15, 2019 12:24 AM
    Hi,

    You can place a slicer on your report, having 2 vales [Current Year / Fiscal Year];

    Now based on your slicer selection you can write a DAX which calculates your measure based on selected value.

    Regards,

    ------------------------------
    Hasham Bin Niaz
    Sr. BI Consultant
    Karachi, Pakistan
    ------------------------------



  • 3.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 08:02 AM
    What would that DAX measure be? I attached a test file to my original post to show what I am looking to accomplish.

    ------------------------------
    Andrew Alexander
    ------------------------------



  • 4.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 11:19 AM
    You can make a copy/paste of the page you are using. Then you put a switch with a bookmark to change from each page. And you change all calendars in the second page to fiscal, so when you publish the dashboard you can have a switch to change of calendar. If you want an example let me know.  Albertoa@wh.farm

    ------------------------------
    Alberto Alvarado
    Business Intelligence
    Amado AZ
    5202819233
    ------------------------------



  • 5.  RE: Using Both Calendar and Fiscal Year in a Report

    Bronze Contributor
    Posted May 15, 2019 02:54 PM
      |   view attached
    I would recommend running two separate calendars, giving each option you want, and then merging them to create the options in a single table for dates.  Don't forget that you will have to reconnect the relationships to point to the new merged table.  This is essentially an UNPIVOT in the Power Query Editor (although that wouldn't be DAX). Option selection without bookmarks. See attached.

    ------------------------------
    Nathan Nelson
    Business Intelligence Developer
    United States
    ------------------------------

    Attachment(s)

    pbix
    TestFile.pbix   94K 1 version


  • 6.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 04:09 PM
    Nathan,

    This looks great! Can you think of any drawbacks to having a many-to-many relationship between my data table and my date table?

    ------------------------------
    Andrew Alexander
    ------------------------------



  • 7.  RE: Using Both Calendar and Fiscal Year in a Report

    Bronze Contributor
    Posted May 15, 2019 04:17 PM

    Hmm...If you start presenting new views or pages that don't requiring selecting either fiscal OR calendar dates, there is a risk you could be inviting a scenario where values were counted twice on the same day (since there are technically two dates every time). One way to keep this clean would be to make the relationship point to a query of your data instead of the original to segregate it (in DAX that would be a simple reference like "NewTableName = OriginalTableName"). 

    I'd also consider putting those new tables in a query group folder so it doesn't get confused and accidentally used where it shouldn't.

    Good luck!



    ------------------------------
    Nathan Nelson
    Business Intelligence Developer
    United States
    ------------------------------



  • 8.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 04:17 PM
    Also, the Previous Year formula doesn't work anymore. Any ideas how to fix that using your solution?

    ------------------------------
    Andrew Alexander
    Finance Analyst
    ------------------------------



  • 9.  RE: Using Both Calendar and Fiscal Year in a Report

    Bronze Contributor
    Posted May 15, 2019 04:28 PM
    Edited by Nathan Nelson May 15, 2019 04:36 PM

    Oops...yeah, that inherited my table renaming. Sorry about that. I think the new formula would just need the table name and a date clarification put back in:

    SalesTotalPY =
    CALCULATE(
              [SalesTotal],
              SAMEPERIODLASTYEAR(Dates[Date].[Date])
    )

    **UPDATE** I just tried that, and, I don't think that is correct.  That measure might have to be rebuilt.

    ------------------------------
    Nathan Nelson
    Business Intelligence Developer
    United States
    ------------------------------
    ----


  • 10.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 04:41 PM
    Or SalesTotalPY = Calculate([SalesTotal], dateadd(Dates[Date].[Date], -1, year))

    ------------------------------
    Alberto Alvarado
    Business Intelligence
    Amado AZ
    5202819233
    ------------------------------



  • 11.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 15, 2019 04:47 PM
      |   view attached
    What I consider is better, is to do a new table where you indicate both calendars, then of that, you can compare or change of calendar. This is an example:

    ------------------------------
    Alberto Alvarado
    Business Intelligence
    Amado AZ
    5202819233
    ------------------------------



  • 12.  RE: Using Both Calendar and Fiscal Year in a Report

    Bronze Contributor
    Posted May 15, 2019 04:44 PM

    Hi Andrew,

    If I'm understanding the objective of your chart, a completely different approach could be to move the Sales Column to your Value, then use the new Dates table column of "Year" as a Legend field in your chart.  Then you can choose which years you want to compare.  Just a thought.

    Good Luck!



    ------------------------------
    Nathan Nelson
    Business Intelligence Developer
    United States
    ------------------------------



  • 13.  RE: Using Both Calendar and Fiscal Year in a Report

    Silver Contributor
    Posted May 24, 2019 10:45 AM
    Hi Andrew,

    Building on Nathan's solution, Alberto's measure for PY works with a slight change:

    SalesTotalPY =
    Calculate(
        [SalesTotal],
        DATEADD(Dates[Date], -1, YEAR),
        FILTER(
            ALL(Dates),
            Dates[Year] <= MAX(Dates[Year]) )
    )

    Here's another solution that you might consider:

    • Create an OptionSelector table using 'Enter Data' with entries Calendar and Fiscal.
    • Create a YearsTable with all possible Year values, both Calendar and Fiscal.  You can do this from Modeling > New Table then:
    YearsTable = 
    DISTINCT(
        UNION(
            VALUES(Dates[Year]), VALUES(Dates[FiscalYearNumber])
        )
    )
    • Connect YearsTable[Year] to Dates[Year], then
    • Connect YearsTable[Year] to Dates[FiscalYearNumber].  This second connection will be inactive, which is what you want.
    • Create new measures for your SalesTotal and SalesTotalPY:
    New SalesTotal = 
    IF(
        FIRSTNONBLANK(OptionSelector[Option], TRUE() ) = "Fiscal",
        CALCULATE(
            [SalesTotal],
            USERELATIONSHIP(Dates[FiscalYearNumber], 'YearsTable'[Year] ) ),
        [SalesTotal]
    )

    New SalesTotalPY =
    IF(
        FIRSTNONBLANK(OptionSelector[Option], TRUE() ) = "Fiscal",
        CALCULATE(
            [SalesTotalPY],
            USERELATIONSHIP(Dates[FiscalYearNumber], 'YearsTable'[Year] ) ),
        [SalesTotalPY]
    )

    • Add OptionSelector and YearsTable as slicers, replace the values on your graph with the new measures and it should work.

    Regards,




    ------------------------------
    Simon Lamb
    IT Consultant, Method Excel Ltd.
    ------------------------------



  • 14.  RE: Using Both Calendar and Fiscal Year in a Report

    Posted May 22, 2019 09:30 PM
      |   view attached
    I suggest the following.

    Append 2 columns to each row of data.  One of the columns, 'CY' is to contain the calendar year within which this data resides.  The other column, 'FY' is to contain the fiscal year within which this data resides.

    Data slicers for 'CY' and 'FY' can be used to alter the displayed data in any visual to display either calendar year data or fiscal year data.  Of course, the user needs to ensure only 'CY' of 'FY' is selected, not both.  (I am not aware of any way to check for multiple slicer selections, ignore one or the other, etc.)

    I have attached an example of this approach.  In this example, the fiscal year begins in April and ends in March.  Fiscal year 2020 covers 1APR2019..31MAR2020.

    I hope this helps.

    ------------------------------
    Steven Wentworth
    SyncraTec Solutions, LLC
    Yardley PA
    215-310-1750
    ------------------------------

    Attachment(s)

    pbix
    CYFYFilters.pbix   42K 1 version