Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measures to create dynamic visualisation that changes based on two run dates selected in filter

Hi,

 

I am trying to create a Power BI visualisation that is able to provide (1) year on year and (2) daily movement in both value and % when I select different run dates from the filter. Basically the whole dataset will have a few run dates across few different years and I need to be able to compare them based on the selected run dates.

I have tried creating a new date table; to form the relationship to the main data table so that it can be used for filter but it doesn't seem to work. I am not sure how what kind of measures or how to approach it further so that this can be achieved. Would you be able to advise?

 

Below is an example of how the main data structure looks like. 

AccountAccount TypeAccount  Type 2Account DescriptionCodeCode Type 1Code Type 2YearMonthOwnerRun dateAmount
AssetCurrentCashCash on Hand15463AWES1202105Ram31 May 20211000
AssetNon CurrentBankCash in Bank35671AWSS2202105Ram31 May 20215000
ExpenseOperatingGeneralGeneral Cost65542ERTT6202105Mary31 May 2021300
ExpenseManufacturingDirectDirect Manufacturing78953EPBT7202105Mary31 May 2021200
AssetCurrentCashCash on Hand15463AWES1202105Ram29 May 2021200
AssetNon CurrentBankCash in Bank35671AWSS2202105Ram29 May 2021200
ExpenseOperatingGeneralGeneral Cost65542ERTT6202105Mary29 May 202150
ExpenseManufacturingDirectDirect Manufacturing78953EPBT7202105Mary29 May 2021100
AssetCurrentCashCash on Hand15463AWES1201905Ram31 May 20195000
AssetNon CurrentBankCash in Bank35671AWSS2201905Ram31 May 20197000
ExpenseOperatingGeneralGeneral Cost65542ERTT6201905Mary31 May 20192200
ExpenseManufacturingDirectDirect Manufacturing78953EPBT7201905Mary31 May 20193000

1 Reply

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Anonymous The recommendation is to create a data model and to normalize your one big table into reasonable dimension and fact tables. Dimension tables could be the calendar, the Owner/Person table, the Account table etc, and the fact table could be the amount and code.  

    With that in place you will be able to use the standard DAX functions for time intelligence. The Quick measures also give you a great starting point for these patterns.