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.

     

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