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,

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

  • Anonymous's avatar
    Anonymous
    Not applicable
    10 years ago

    Hi Anonymous,

     

    Sorry, I was being an idiot - I was creating a calculated column, not measure! Oops...

     

    When you have a moment, I'm still interested though - what relationship(s) did you define between DIM_DATE and FACT_PLANS? I ask because I current use DIM_DATE(date) and FACT_PLANS(Plan Start Date) and it doesn't quite return the correct values.

     

    Thanks again :)

     

    Pbix

    • Anonymous's avatar
      Anonymous
      Not applicable
      10 years ago

      Hi Anonymous,

      I just copy your sample data and enter it to Power BI Desktop, no relationship is created between the tables. What is the relationship between your tables? And what does your measure look like?

      Thanks,
      Lydia Zhang