Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Tooley
Frequent Visitor

DAX help needed - create measure, sum value for same month in a previous report (relative to month)

What measure can I use to sum amount for the same month from a previous report, but the previous report is conditional based on an Accounting Period End Date (i.e., report date) and Period End Date (i.e. month).  The following example hopefully explains the logic:

 

Accounting Period End date (a)Period End date (b)LogicSource Accounting Period End date report for Prev MonthComment
30 Jun 2230 Apr 22b < a30 Mar 22Last forecast for Apr.  May & June (and subsequent) reports contain Apr actuals
30 Jun 2230 May 22b < a30 Apr 22Last forecast for May.  June (and subsequent) reports contain May actuals.
30 Jun 2230 Jun 22b = a30 May 22Last forecast for Jun.  June report contains first June actuals.
30 Jun 2230 Jul 22b > a30 May 22Last data available.  July is future month, forecast can change
30 Jun 2230 Aug 22b > a30 May 22Last data available.  Aug is future month, forecast can change

 

The Link to "Previous Month Forecast.pbix"  provides dummy Forecast data with 12 reports for a single year, which includes expected amounts.  The only other table is Dates (relationship = Dates[Date] 1:* Forecasts[Period End Date]).  Users select a single Accounting Period End Date via a slicer and results should be based on this date.  

 

To better illustrate, I've achieved the expected result by adding a calculated column to lookup the previous amount, however this will be unreliable as depends on unique records (duplications will occur if multiple reports are uploaded for the same Accounting Period End Date, which is highly likely), plus I would prefer to use a measure.  Notes are also included in the calculated column, e.g.:

  • Forecasts
    • the previous value should be taken from the previous report date
    • For July report, the previous value for Aug, Sept, Oct, Nov & Dec is taken from June report
  • Actuals
    • As values are frozen, we must get last forecasted value (not actual), i.e. from the previous report relative to the month
    • The previous value for June month is from May report, July from June, August from July etc.

The closest I've got is the following measure (but I've hit a brick wall and cannot figure out how to apply the rules for future Period End dates v Accounting Period End dates in a measure):

 

Prev Total Forecast (Measure) =
CALCULATE(
    SUM( Forecasts[Value] ),
    ALL( Forecasts[Accounting Period End date] ),
    Forecasts[Accounting Period End date] = EOMONTH(Forecasts[Period End date],-1)
)

 

A page is included in the pbix file with the Accounting Period End date slicer and a simple pivot table to show results.  The following is based on a selected Accounting Period End date of 30th June 2022:

 

MonthForecastActualTotal ForecastExpected Prev Total ForecastPrev Total Forecast (Calc Col)Prev Total Forecast (Measure)
Jan £1£1£0  
Feb £202£202£2£2£2
Mar £303£303£203£203£203
Apr £404£404£304£304£304
May £505£505£405£405£405
Jun £606£606£506£506£506
Jul£607 £607£507£507£607
Aug£608 £608£508£508£708
Sep£609 £609£509£509£809
Oct£610 £610£510£510£910
Nov£611 £611£511£511£1,011
Dec£612 £612£512£512£1,112

 

Any help or steer in the right direction would be greatly appreciated.

 

Thanks 🙂

2 REPLIES 2
amitchandak
Super User
Super User

@Tooley , It is always better to use date table for time intelligence 

 

MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))
MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD('Date'[Date]))
last MTD Sales = CALCULATE(SUM(Sales[Sales Amount]),DATESMTD(dateadd('Date'[Date],-1,MONTH)))
last month Sales = CALCULATE(SUM(Sales[Sales Amount]),previousmonth('Date'[Date]))

 

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

Hi amitchandak,

 

Thanks for responding and making the point about using time intelligence on the date table.

 

The DAX examples you kindly provided don't solve the problem, and I couldn't see one in the training video links.

 

Apologies if I didn't explain clearer in my original post.  What I need is not strictly the value from last months report, but a value from a prior report relative to both the "Accounting Period End date" and "Period End date" selected.  The followingpoints and supporting picture will hopefully help better explain (note, all values are for the June month).

 

  • When selecting an "Accounting Period End date" upto the June "Period End date", the June value in the prior report should be expected.
  • When selecting an "Accounting Period End date" after the June "Period End date", the June value in the prior May report should be expected.

 

June Example.png

 

If I had the following table in Excel:

Excel example.PNG

The Excel formula in the "Prev Value...." column might be:

 

Excel formula.PNG

 

How can this be achieved in a DAX measure?

 

Thanks

 

Tooley

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.

Top Solution Authors