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. there were 500 clients open to the team on 31st May 2016.

 

What is the best approach for managing these types of data (which typically can't be aggregated as it just represents a snapshot position on any given day) in a cube? I've found that Power BI tries to incorrectly aggregate these data in visualisations (e.g. aggregate 12 month end snapshots into a summed single year figure, which isn't correct).

 

Thanks

 

Pbix

Need Help
Tips and Tricks
Tutorial Requests

7 Replies

  • ankitpatira's avatar
    ankitpatira
    Community Champion
    10 years ago

    Anonymous I would say you can use table or matrix visual to represent such data and make sure you set fields as Do Not Summarize. This will give you raw data without aggregation. You can also use other visuals which support Do Not Summarize.

  • 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