Forum Discussion

PeterParker's avatar
PeterParker
Icon for Helper I rankHelper I
4 years ago
Solved

Special Timeperiod

Hello everyone, i need your help. Some key figures have to be calculated every quarter, at first this sounds simple. However, values ​​from the past 3 quarters (one year in total) should be output i...
Tutorial Requests
  • v-yanjiang-msft's avatar
    v-yanjiang-msft
    4 years ago

    Hi PeterParker ,

    I haven't created a calendar, here's my solution.

    1.Create three columns.

    YearQuarter = FORMAT('Table'[Date],"YYYY-Q")
    Rank = RANKX('Table','Table'[YearQuarter],,ASC,Dense)
    Quarter = 
VAR _BeginnQrt =
    DATEADD ( 'Table'[Date], -3, QUARTER )
VAR _Spezial_Qrt =
    "Qtr" & FORMAT ( _BeginnQrt, "Q YYYY" ) & " - Qtr"
        & FORMAT ( 'Table'[Date], "Q YYYY" )
RETURN
    IF ( ISBLANK ( _BeginnQrt ), BLANK (), _Spezial_Qrt )

    2.Create a measure.

    Sum value = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Rank]
            IN {
                MAX ( 'Table'[Rank] ),
                MAX ( 'Table'[Rank] ) - 1,
                MAX ( 'Table'[Rank] ) - 2,
                MAX ( 'Table'[Rank] ) - 3
            }
    ),
    'Table'[Value]
)

    3.Get the expected result.

     

    I attach my sample below for reference.

    Best Regards,
    Community Support Team _ kalyj

    If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

     

PeterParker's avatar
PeterParker
Icon for Helper I rankHelper I
4 years ago

Morning v-yanjiang-msft 

 

How did you realise this?
Have you created a new second calendar table?

 

Regards PeterParker

v-yanjiang-msft's avatar
v-yanjiang-msft
Icon for Community Support rankCommunity Support
4 years ago

Hi PeterParker ,

I haven't created a calendar, here's my solution.

1.Create three columns.

YearQuarter = FORMAT('Table'[Date],"YYYY-Q")
Rank = RANKX('Table','Table'[YearQuarter],,ASC,Dense)
Quarter = 
VAR _BeginnQrt =
    DATEADD ( 'Table'[Date], -3, QUARTER )
VAR _Spezial_Qrt =
    "Qtr" & FORMAT ( _BeginnQrt, "Q YYYY" ) & " - Qtr"
        & FORMAT ( 'Table'[Date], "Q YYYY" )
RETURN
    IF ( ISBLANK ( _BeginnQrt ), BLANK (), _Spezial_Qrt )

2.Create a measure.

Sum value = 
SUMX (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Rank]
            IN {
                MAX ( 'Table'[Rank] ),
                MAX ( 'Table'[Rank] ) - 1,
                MAX ( 'Table'[Rank] ) - 2,
                MAX ( 'Table'[Rank] ) - 3
            }
    ),
    'Table'[Value]
)

3.Get the expected result.

 

I attach my sample below for reference.

Best Regards,
Community Support Team _ kalyj

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

  • PeterParker's avatar
    PeterParker
    Icon for Helper I rankHelper I
    4 years ago

    Hi v-yanjiang-msft ,

    Hello,

    brilliant.😎

     

     

    Now, I only have the problem with sorting in a diagram according to the New time row.
    If I want to sort the "Quarter" column by "Rank", I get an error message.

     

    Regrads Peter 

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi PeterParker ,

      My rank column is based on YearQuarter, not Quarter, you can download my sample for reference.

      Best Regards,
      Community Support Team _ kalyj

      • PeterParker's avatar
        PeterParker
        Icon for Helper I rankHelper I
        4 years ago

        Hi v-yanjiang-msft,

         

        thank you for you reply.

         

        I have found the problem. In the "Quarter" Column was some blank fields.

        Now, i can sort the "Quarater" by "YearQuarter".

         

         

        Thank you so much for your help.

         

        RG Peter 

         

  • PeterParker's avatar
    PeterParker
    Icon for Helper I rankHelper I
    4 years ago

    Hi v-yanjiang-msft ,

     

    i have a question. I cant use a measure in this code.
    Have you any idear how can i solve this?

     

     

    Sum value 2 = 
CALCULATE (
    FILTER (
        ALL ( 'Table' ),
        'Table'[Rank]
            IN {
                MAX ( 'Table'[Rank] ),
                MAX ( 'Table'[Rank] ) - 1,
                MAX ( 'Table'[Rank] ) - 2,
                MAX ( 'Table'[Rank] ) - 3
            }
    ),
    Measure
)

     

    And can you show me how can i upload a pbix file in the post?

    Regards Peter

    • v-yanjiang-msft's avatar
      v-yanjiang-msft
      Icon for Community Support rankCommunity Support
      4 years ago

      Hi PeterParker ,

      Did you mean you can't use the measure marked in red line in the code? Actually it's a column in my formula.

      About how to upload a pbix. file, you can refer to this link: Unable to upload Pbix files to community

       

      Best Regards,
      Community Support Team _ kalyj