Forum Discussion

BI_diva's avatar
BI_diva
Regular Visitor
3 years ago

How to get previous trimester

Hi,

 

I need to make a comparison to the previous trimester (months 1-4; 5-8; 9-12) for the trimester that is selected in the graph by the end user.

After trying several things, I am not getting the value (DISTINCTCOUNT) I am looking for.

Please advice.

 

  CountToT =
VAR _Trimester = MAX(DIM_Date[Trimester])
VAR _Calculation1 =
 CALCULATE(
  DISTINCTCOUNT('Fact'[FactKey]),
  FILTER(
   ALLSELECTED(DIM_Date),
   DIM_Date[Trimester] = _Trimester -1
  )
)
RETURN
_Calculation1

   

Desired result = 757

I do get the correct result for the same trimester last year by using this formula:

CALCULATE(
DISTINCTCOUNT('Fact'[FactKey]),
DATEADD(DIM_Date[Date],-1,YEAR)
)

1 Reply

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

    BI_diva , Create a new column in Dim_date , Mark it as date table too

     

    Year Trimester = year([Date]) * 100 + [Trimester]

     

    Then have rank column

     

    Trimester Rank = RANKX('dim_Date','Date'[Year Trimester],,ASC,Dense)

     

    These measures can help. Change the sum expression as per need
    Trimester Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Trimester Rank]=max('dim_Date'[Trimester Rank])))
    Trimester Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Trimester Rank]=max('dim_Date'[Trimester Rank])-1))

     

    This Week= CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Year]=max('dim_Date'[Year]) && 'dim_Date'[Trimester] = Max('dim_Date'[Trimester]) ))
    Last Year Week = CALCULATE(sum('Table'[Qty]), FILTER(ALL('dim_Date'),'dim_Date'[Year]=max('dim_Date'[Year])-1 && 'dim_Date'[Trimester] = Max('dim_Date'[Trimester])))

     

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

     

    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