Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
10 years ago

Snapshot data in Power BI

Hi All,   I have a question about modelling data using Power BI.   In my industry we frequently report on the number of clients open to a staff member, team or service on any given day - e.g. the...
Need Help
Tips and Tricks
Tutorial Requests
Anonymous's avatar
Anonymous
Not applicable
10 years ago

Hi Anonymous,

Could you please share sample data of your scenario and post the expected result? Also we need to know that what visualizations you are using.

Thanks,
Lydia Zhang

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi Both,

     

    Thanks for your replies. Sure, here's some simple data replicating my scenario. The eventual model would have multiple tables but the two most relevant tables are:

     

    DIM_DATE and

    FACT_PLANS

     

    DIM_DATE is a date dimension, like:

     

    DateYearMonthDay
    01/03/2016201631
    02/03/2016201632
    03/03/2016201633
    04/03/2016201634
    05/03/2016201635
    06/03/2016201636
    07/03/2016201637
    08/03/2016201638
    09/03/2016201639
    10/03/20162016310
    11/03/20162016311
    12/03/20162016312
    13/03/20162016313
    14/03/20162016314

     

    and FACT_PLANS is a table like the below (with 1 million rows)

     

    Client IDPlan TypePlan Start DatePlan End Date
    1A01/01/201431/03/2016
    2B01/03/201605/03/2016
    3C02/03/201612/03/2016
    4D01/04/201405/01/2015
    5B31/05/201008/03/2016
    6D01/08/201101/11/2015
    7E01/05/201509/05/2016
    8A14/07/201511/03/2016
    9A14/05/201531/03/2016
    10C15/05/201314/04/2016

     

    What I would like to do is work out the number of plans open on any given day, for an expected result like:

     

    DateNumber of plans open
    01/03/20167
    02/03/20168
    03/03/20168
    04/03/20168
    05/03/20168
    06/03/20167
    07/03/20167
    08/03/20167
    09/03/20166
    10/03/20166
    11/03/20166
    12/03/20165
    13/03/20164
    14/03/20164

     

    What is the most performant way to dynamically claculate this using Power BI desktop, for any given date? From a visualisation perspective I'm thinking a plain bar or line chart.

     

    Thanks!

     

    Pbix

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi Anonymous,

      You can create a measure named plan number in your FACT_PLANS table using the following formula. I test it in my environment which is shown in the screenshot below .

      plan number = CALCULATE(COUNTROWS(FACT_PLANS),

         FILTER(FACT_PLANS, (FACT_PLANS [Plan Start Date] <= LASTDATE(DIM_DATE[Date])

             && FACT_PLANS [Plan End Date]>= FIRSTDATE(DIM_DATE[Date]))))



      Then create line chart by using the Date field and plan number measure as follows.


      Thanks,
      Lydia Zhang

      • Anonymous's avatar
        Anonymous
        Not applicable
        10 years ago

        Hi Anonymous,

         

        Thanks for the reply - really helpful. However, I'm having trouble replictating it in my environmnent - for me the measure returns a count of the total number of rows in the table in every row (though your example clearly works)!

         

        What relationship are you using between FACT_PLANS and DIM_DATE?

         

        Thanks alot,

         

        Pbix