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 5 days ago
      |   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 4 days ago
    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 4 days ago
    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 4 days ago
    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

    Posted 4 days ago
      |   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 4 days ago
    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

    Posted 4 days ago

    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 4 days ago
    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

    Posted 4 days ago
    Edited by Nathan Nelson 4 days ago

    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 4 days ago
    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 4 days ago
      |   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

    Posted 4 days ago

    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
    ------------------------------