Nigeria Modern Excel & Power BI User Group

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

SOS with DAX

  • 1.  SOS with DAX

    Posted Nov 06, 2019 08:20 AM
    Hello All,

    I am very new to DAX but have made some progress in the last one week. I currently have an Excel Dashboard I am trying to replicate in Power BI.

    I am trying to create some visualizations which would have the following;

    1. Revenue MTD | MTDLY  | YTD  | YTDLY
    2. Royalty MTD  | MTDLY  | YTD  | YTDLY
    3. DD&A MTD  | MTDLY  | YTD  | YTDLY
    MTD = Month To Date
    MTDLY = MTD Last Year
    YTD = Year To Date
    YTDLY = YTD Last Year

    image.png
    Each line item is filtered by different account codes e.g

    1. Revenue  Account Code 500010
    2. Royalty = Account Code 660010
    3. CHA = Account Code 620500 to 620999
    4. Finance = Account code (800000 to 809999) + Account code 621020.

    I was able to calculate Revenue MTD as " ThisPeriodRevenue = CALCULATE([TotalAmount],FILTER(Ledger,Ledger[ACCNT_CODE]="500010")) " and 
    Royalty MTD as " ThisPeriodRoyalty = CALCULATE([TotalAmount],FILTER(Ledger,Ledger[ACCNT_CODE]="660010")) " which worked fine for 1-11 shown below when Period and ANAL_T0 filters are updated/changed.
    as shown below;


    image.png

    Item 12 is not correct because it was meant to be filtered by multiple ANAL_T0 values of 201 and 202. This is my first problem as I have tried to update the filter by this setting below + some other options but not working. I think I need to disconnect 12 from the current page filter and apply the one below but I don't know-how. 

    image.png

    Here is the DAX I wrote for 22 series (item 12) 
    22 Series This Period = CALCULATE([TotalAmount],FILTER(Ledger,Ledger[ACCNT_CODE]>= "220000" && Ledger[ACCNT_CODE]<= "224999")) but it captures only Total Amount for 202, leaving out 201.
    Once this is sorted, I want to be able to calculate DAX for
    1. Revenue MTD | MTDLY  | YTD  | YTDLY - filtered by account code 500010
    2. Royalty MTD  | MTDLY  | YTD  | YTDLY - filtered by account code 660010
    3. DD&A MTD  | MTDLY  | YTD  | YTDLY - filtered by account code 500010
    Lastly, please, is there a way I can create the Excel table above in PowerBI? PoP is "This Period" - "This Period Last Year" and YOY = "YTD" - "YTD Last Year"
    Thank you for your assistance.

    Regards,

    Oladapo Sorinola
    Data enthusiast. 



  • 2.  RE: SOS with DAX

    Posted Nov 15, 2019 03:28 AM
    Hi Oladapo,

    From our phone and video call discussions, we used the DAX function TOTALYTD but this was not sufficient to solve it all.
    I believe the Data Model and the Structure of the Data may be the problem.

    Kindly ensure your data is clean and the Model is perfect. Or we schedule another meeting for more investigation on the data.

    Warm regards.

    ------------------------------
    Adewale Yusuf
    Business Intelligence Trainer/Data Analyst
    Lagos
    8168767786
    ------------------------------



  • 3.  RE: SOS with DAX

    Posted Jan 23, 2020 04:56 AM
    Hello Adewale Yusuf,

    Thank you for your assistance and my apologies for just getting back to you. This is resolved now (as shown below) and I owe it to the entire DBrown team.

    Gracias.
    Dashboard


    ------------------------------
    Oladapo Sorinola
    Data Analyst
    Nigeria
    ------------------------------



  • 4.  RE: SOS with DAX

    Posted Jan 23, 2020 06:53 AM
    Hi Oladapo,

    You are most welcome.

    We will always be available if you need any other assistance in the future.

    Thanks.

    ------------------------------
    Adewale Yusuf
    Business Intelligence Trainer/Data Analyst
    Lagos
    8168767786
    ------------------------------



  • 5.  RE: SOS with DAX

    Silver Contributor
    Posted Nov 15, 2019 05:34 AM

    Dear Oladapo,

     

    Well done on your efforts. Right away I see the issue you are having. You are attempting to use the account codes as the basis for your calculations. This is not best practice and will lead to a lot of errors. In Power BI you must use tables and relationships to accomplish what you want. It is much better to build out a chart of accounts with columns that mimic the exactly structure of the reports you want to replicate. You can then simply drag these columns into the Pivot Table in Excel or the Table or Matrix visual in Power BI to visualize the report you have below.

     

    First you must build an efficient data model. A Data Model wires up the various table of data you will need to run your reports seamlessly. A good data model also simplifies the DAX formulas you will need to run your reports.

     

    Data Model

    You will need at a the following tables and columns at the minimum. Do you have them?

    1. GL: Date | GL Account Code | Transaction ID (if any) | Sign (Dr or CR) | Amount (note, this assumes that you are using a single column trial balance and the amounts have negative values for debits and positive values for credits)
    2. Date: You will need a Date Table, you can easily create this using DAX. For a post on how to do this  click here "How to Build a simple Date Table with DAX"
    3. COA: COA (Chart of Accounts) table is the secret tool you have for creating reports in the exact format you want. Your COA Table will have the following columns: GL Account Code | Category 1 | Sort Order 1 | Category 2 | Sort Order 2 and so on.

     

    The categories are the names of the various account heads depending on how granular you want the report and the sort orders are numbers depicting how you want to show the accounts in your report. For example Category one can have just two item (Profit & Loss Account, Balance Sheet) and category 2 can have (Revenue, Expenses, Assets, Liabilities) and you keep going down in granularity depending on how you want your reports structured.

     

    All accounting packages come with a chart of accounts, all you need to include is the sort order columns which contain serial numbers (1,2,3,4 etc) to define the order in which you want to see your line items in your report. If for instance you want to see Revenue before Expense, then revenue will have a sort order number of 1 and Expenses a sort order number of 2.

     

    Create Relationships

    Once you have the three tables above. You can create simple relationships between them as follows:

    1. GL connects to COA via the "GL Account Code" column
    2. GL connects to Date via the "Date" Column

     

    DAX

    Anchor Measure

    Simple summation of the Amount column of your GL Code table.

    Amount = SUM(GL[Amount])

     

    Time Intelligence Measure

    These are the set of formulas that will give you what you require. Note, for them to work effectively in Power BI, you must define the table as the default Date table for your model. You do this by selecting the Date table from your Data View, then go to the Modeling Tab in the Ribbon and click on the button to the right called "Mark as Date Table", it will prompt you to select the column containing date in the date table, do so. This will open up the power of time intelligence in your reporting.

     

    Below is the structure of the formulas you will need.

    MTD = TOTALMTD([Amount],'Date'[Date])

    MTDLY = TOTALMTD([Amount], SAMEPERIODLASTYEAR('Date'[Date]))

    YTD = TOTALYTD([Amount],'Date'[Date])

    YTDLY = TOTALYTD([Amount], SAMEPERIODLASTYEAR('Date'[Date]))

    POP MTD = [MTD] – [MTDLY]

    POP YTD = [YTD] – [YTDLY]

     

    With all of the above done, you reports is a simple matter of:

    1. Insert the Matrix Visual as your report (or a pivot table from Power Pivot in Excel)
    2. Insert your report lines by selecting the appropriate column from your chart of accounts table; Place this in Row under the matrix visual in Power BI or Pivot table in Excel
    3. Insert a slicer for Date, select Date from the newly created Date Table: Select the precise report date you require
    4. Insert the DAX formulas you want to report by under columns in the matrix visual or column in Pivot Table

     

    How to Build a simple Date Table with DAX

     

    To Build a Date Table using DAX in Power BI, follow these steps:

     

    1. Go to the Modeling Tab in Power Bi and Select "New Table"
    2. Copy and paste the entire code below. (Note: You must already have a table called "GL" that contains all your transactions which also has a column called Date. See my notes above. If your table is not called GL, then modify the code below appropriately.

     

    Date =

    VAR MinYear = YEAR ( MIN ( GL[Date]) )

    VAR MaxYear = YEAR ( MAX ( GL[Date] ) )

    RETURN

    ADDCOLUMNS (

        FILTER (

            CALENDARAUTO( ),

            AND ( YEAR ( [Date] ) >= MinYear, YEAR ( [Date] ) <= MaxYear )

        ),

        "Year", YEAR ( [Date] ),

        "Month Name Long", FORMAT ( [Date], "mmmm" ),

        "Mth Name Short", FORMAT ( [Date], "mmm" ),

        "Month Number", MONTH ( [Date] ),

        "Weekday Long", FORMAT ( [Date], "dddd" ),

        "Weekday Short", FORMAT ( [Date], "ddd" ),

        "Weekday number", WEEKDAY( [Date] ),

        "Quarter", "Q" & TRUNC ( ( MONTH ( [Date] ) - 1 ) / 3 ) + 1)

     

    Let us know if this helps.

     

    Cheers



    ------------------------------
    David Brown
    PUG Leader
    Microsoft MVP
    ------------------------------



  • 6.  RE: SOS with DAX

    Posted Dec 12, 2019 07:49 AM
    Dear Sir,

    I wish I could write "THANK YOU" in DAX :). 

    Yes, I have my tables, GL (Ledger), COA  with each of the columns mentioned, the data was extracted from Infor SunSystems, a financial system. Kindly allow me review your recommendations and I will get back to you. Adewale Yusuf has been helpful as well and I appreciate the team. 

    Again, thank you!

    Kind regards,

    Oladapo Sorinola 
    Data Enthusiast. 





    --
    Regards,

    Oladapo Sorinola
    Infor SunSystems/Microsoft Excel Consultant
    Business Intelligence & Data Services
    +2347014282477,+2347062932708