Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
3 years ago
Solved

Current data and quarter trends

Hi Everyone!

 

I have a table that I get daily feeds on. I plan to append this when uploading to power BI with my folder as a source. Example appended table below. The status of each item is updated at random dates throughout the year.

NameStatus (String)Status Date
AppleStage 016-Dec-21
AppleStage 116-Feb-22
AppleStage 228-Sep-22
AppleStage 315-Nov-22
BerryStage 016-Dec-21
BerryStage 101-Jan-22
BerryStage 225-Apr-22
CherryStage 016-Dec-21
CherryStage 129-Mar-22
CherryStage 230-May-22
CherryStage 327-Dec-22

 

What I want to do is to be able to show quarterly report. Example below:

Say, if I am report as of Q4 2022: 

 

 CurrentQ-1Q-2Q-3PY
NameQ4 2022Q3 2022Q2 2022Q1 2022Q4 2021
AppleStage 3Stage 2Stage 1Stage 1Stage 0
BerryStage 2Stage 2Stage 2Stage 1Stage 0
CherryStage 3Stage 2Stage 2Stage 1Stage 0

 

I am thinking of assigning quarters for each row item but that doesn't really work because I want to carry on the same data when the status of the item didn't change e.g. Q1 and Q2 and was only changed on Q3. 

 

I want to apply the above with a timeslider as well where I can show the status of the specific time I have chosen.

 

Once the above is solved, my 2nd question is my quarters is not the calendar quarter. How can I tailor the quarter based on the unique range we had in the organization.

 

Thanks for your help!

 

Regards,

CV

 

Show and Tell
Tutorial Requests

7 Replies

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

    Use measures to calculate the latest value for each name and date.

     

    Specify your fiscal calendar rules.

    • Anonymous's avatar
      Anonymous
      Not applicable
      3 years ago

      I'm trying to count how many items are in a given stage as of date. I have this formula but it doesn't work:

      M_stage1=
      var maxdate = CALCULATE(MAX('table'[status date]),ALLEXCEPT('table','table'[Name]))
      return
      Calculate(
          DISTINCTCOUNT('table'[Name]),
          Filter('table','table'[Last modified]=maxdate && 'table'[Status] <> "Stage 1"))
       
      The formula above comes up as blank. I'm not sure why... can you please correct above?
      • lbendlin's avatar
        lbendlin
        Icon for Super User rankSuper User
        3 years ago

        See attached for the required plumbing.  You will have to decide what a good visualization would be for this.