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 in each quarter. Furthermore, key figures should also be shown in the course. At this point I have problems, for the calculation of the current quarter including the 3 previous ones, I created a calculated column in the calendar that determines this period. 

 

Does anyone have any idea how I could create a history of the metrics? Or in a line chart or bar chart could represent?

 

Qrt 1 2020 - Qrt 4 2020Qrt 2 2020 - Qrt 1 2021Qrt 3 2020 - Qrt 2 2021Qrt 4 2020 - Qrt 3 2021Qrt 1 2021 - Qrt 4 2021Qrt 2 2021 - Qrt 1 2022Qrt 3 2021 - Qrt 2 2022
MeasureMeasureMeasureMeasureMeasureMeasureMeasure

 

I thank you in advance for your help.

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.

     

11 Replies

  • amitchandak's avatar
    amitchandak
    Icon for Super User rankSuper User
    4 years ago

    PeterParker , you can get gradient based on measure, if you need based on QTR Name, you can use measure based color using field value option , but you have choose color using hexcode like #FFF001

     

    example with color name, you can use hexcode

     

    Switch(True() ,

    max(Table[Qtr Name]) = "Qtr 4", "Green",

    max(Table[Qtr Name]) = "Qtr 3", "Light Green",

    // add others 

    )

     

    How to do conditional formatting by measure and apply it on pie?: https://youtu.be/RqBb5eBf_I4

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

      Hi amitchandak,

       

      thanks for your answer. 

      I have a mistake in my Post, i mean:

       

      Does anyone have any idea how I could create a history of the metrics? Or in a line chart or bar chart could represent?

       

      BR Peter

  • PeterParker's avatar
    PeterParker
    Icon for Helper I rankHelper 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
      Icon for Community Support rankCommunity 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
        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