Forum Discussion

PeterParker's avatar
PeterParker
Helper 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
Helper I
4 years ago

This is my try :

 

 

Test 2 = 
VAR AktDate = 
    Kalender[Date]
var BeginnQrt = 
    DATEADD(Kalender[Date] ,-3, QUARTER )
var Spezial_Qrt = FORMAT(BeginnQrt, "YYYY ") &  "Q" &FORMAT(BeginnQrt, "Q") & " - " &FORMAT(AktDate,"YYYY") & " Q"  &FORMAT(AktDate,"Q")
RETURN
Special_Qrt

 

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

Hi PeterParker ,

Have you got the expexted outcome by your formula? If not, I create a sample below.

Is this your expected outcome?

Or could you please explain more about your sample?

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
    Helper 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
      Community 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
        Helper 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