Forum Discussion

MacedoLuis's avatar
MacedoLuis
New Member
3 years ago

MAT Based Previous Quarters

vl_sales year quarter Categoty 113mi 2021 1 Kit Kat 114mi 2021 2 Nescau 100mi 2021 3 Chocolate 87mi 2021 4 Nescau 88mi 2022 1 Beve 115mi 2022 2 Iorgurt 77...
Need Help
amitchandak's avatar
amitchandak
Super User
3 years ago

MacedoLuis , Create a new Rank column in dim calendar (calling it date in my formula)

 

Qtr Rank = RANKX(all('Date'),'Date'[quarter_list],,ASC,Dense)

 

 

Then you can have measure like
This Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])))
Last Qtr = CALCULATE(sum('Table'[Qty]), FILTER(ALL('Date'),'Date'[Qtr Rank]=max('Date'[Qtr Rank])-1))

 

 


Time Intelligence, Part of learn Power BI https://youtu.be/cN8AO3_vmlY?t=27510
Time Intelligence, DATESMTD, DATESQTD, DATESYTD, Week On Week, Week Till Date, Custom Period on Period,
Custom Period till date: https://youtu.be/aU2aKbnHuWs&t=145s

 

 

Power BI Custom Period Till Date (PTD)- https://youtu.be/rQ3Z_LtxwQM

LuisMacedo's avatar
LuisMacedo
Regular Visitor
3 years ago

amitchandak  I tried it, and got blanks. And also tried like this, but i got blanks. Would you have another to calculate it?

fx_MovingAnnualTotal =
VAR fx_calendartable =
    SUMMARIZE(DimCalendario,
        DimCalendario[Data],
        DimCalendario[Quarter],
        DimCalendario[Trim_Ano],
        "@Qtr Rank",
        RANKX(ALL(DimCalendario), DimCalendario[Trim_Ano],,ASC,Dense)
    )
VAR fx_LastQtr =
    CALCULATE([Faturamento na Venda], FILTER(fx_calendartable, [@Qtr Rank] = MAXX(fx_calendartable, [@Qtr Rank])-1))
RETURN
fx_LastQtr