Forum Discussion

Deligraphs's avatar
Deligraphs
Regular Visitor
2 years ago
Solved

Dynamic Before Previous Month and Further

Hi Everyone,

 

I have a Report Date column that looks like this: 

 

 
Jun-24
Mar-24
Dec-23
Sep-23
Jun-23

 

I have a measure that calculates this:

 

$Sample = SUM(Sample)*1000000
 
 
Im currently showing that report dates separately by using the following:
 
PrevReportDate_1 = PREVIOUSDAY('04DateTable'[Date])
 
PrevReportDate_2 =
VAR Previous2 = PREVIOUSDAY('04DateTable'[Date])
VAR RESULTS = Previous2 - 92
RETURN RESULTS
 
PrevReportDate_3 =
VAR Previous3 = PREVIOUSDAY('04DateTable'[Date])
VAR RESULTS = Previous3 - 184
RETURN RESULTS
 
PrevReportDate_4 =
VAR Previous3 = PREVIOUSDAY('04DateTable'[Date])
VAR RESULTS = Previous3 - 275
RETURN RESULTS
 

 

 

I can get the June 2024 result by using the $Sample measure itself and March 2024 results using PREVIOUSDAY but for December 2023 onwards,  it does not provide the expected result.

 

When I am trying to incorporate the PrevReportDate Dax to my $Sample sum measure, it does not provide an accurate result but rather, giving us the current month's result. Can anyone recommend a workaround? 

 

I was using this DAX to try to get the December 2023 results but it is showing June 2024's data:

 

aPrevNSOPrevHeadroomOutstanding_92 =
VAR Previous2 = PREVIOUSDAY('04DateTable'[Date])
VAR Prev92Days = DATEADD(Previous2, -92, DAY)
VAR Calc = SUM($Sample])*1000000
VAR RESULTS = CALCULATE(Calc, '04DateTable'[Date] = Prev92Days)
RETURN RESULTS

 

Thanks!

Need Help
Tips and Tricks
  • fahadqadir3's avatar
    fahadqadir3
    2 years ago

    Deligraphs  Use PARALLELPERIOD to go back specific numbers of months, quarters, or years:

    When calculating values for previous periods, apply the PARALLELPERIOD or DATEADD functions directly in your calculation measure:

    aPrevNSOPrevHeadroomOutstanding_92 =
    VAR PrevPeriod = CALCULATE(MAX('04DateTable'[Date]), PARALLELPERIOD('04DateTable'[Date], -3, MONTH))
    VAR Calc = SUM(Sample) * 1000000
    VAR Results = CALCULATE(Calc, '04DateTable'[Date] = PrevPeriod)
    RETURN Results

    Adjust PARALLELPERIOD to MONTH, QUARTER, or YEAR as needed, based on your report’s date structure. This method should yield correct results for each report date, regardless of the period gaps.

     

    Deligraphs  Hope it works.

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!! 

1 Reply

  • fahadqadir3's avatar
    fahadqadir3
    Icon for Solution Supplier rankSolution Supplier
    2 years ago

    Deligraphs  Use PARALLELPERIOD to go back specific numbers of months, quarters, or years:

    When calculating values for previous periods, apply the PARALLELPERIOD or DATEADD functions directly in your calculation measure:

    aPrevNSOPrevHeadroomOutstanding_92 =
    VAR PrevPeriod = CALCULATE(MAX('04DateTable'[Date]), PARALLELPERIOD('04DateTable'[Date], -3, MONTH))
    VAR Calc = SUM(Sample) * 1000000
    VAR Results = CALCULATE(Calc, '04DateTable'[Date] = PrevPeriod)
    RETURN Results

    Adjust PARALLELPERIOD to MONTH, QUARTER, or YEAR as needed, based on your report’s date structure. This method should yield correct results for each report date, regardless of the period gaps.

     

    Deligraphs  Hope it works.

    Did I answer your question? Mark my post as a solution! This will help others on the forum!
    Appreciate your Kudos!!