Current Month and Prior Month (Table, Funnel and Cards)

[PowerNooby]

Hi Folks,

I need help to create a DAX  Measures for Prior Month's value to display in Tables & Funnels and Cards visuals.

I tried with 2 measures without success.

Prior Month 1 = CALCULATE(SUM(Table1[Opportunity Value]),PREVIOUSMONTH(Table1[Snapshot Date]) 

PriorMonth 2 =

VAR selecteddate = IF(HASONEFILTER(Table1[Snapshot Date]),SELECTEDVALUE(Table1[Snapshot Date]))

Return

IF(NOT(ISBLANK(selecteddate)),CALCULATE(SUM(Table1[Opportunity Value]),INTERSECT(CALCULATETABLE(VALUES(Table1[Opportunity Name]),Table1[Expected Opportunity Close Date],Table1[Snapshot Date] = EDATE(selecteddate,-1)),CALCULATETABLE(VALUES(Table1[Opportunity Name]),Table1[Expected Opportunity Close Date],Table1[Snapshot Date]))))

Required

When selected a value in Snaphsot Date, the Prior Month's table will display the list of Opportunities and Total Opportunity Value of Prior Month's snapshot data.  Similar values will display in Funnel, & Card (Total Opportunity Value & Count of Opportunities) visuals.

 

If select a category (e.g., Stage) in the Current Month's Funnel visual, the Prior Month's Table, Funnel and Cards will filter to the selected Stage.  This also applies when selected the category (e.g., Stage) in Prior Month's Funnle.

The above Prior Month's visuals are displayed with a separate Snapshot Date slicer for illustration.  In actual presentation, only require 1 Snapshot Date slicer.

Link to PBIX file - https://drive.google.com/file/d/1s0txnBGrpd0xmkJdwzR8FTUSPEhxQ0Ng/view?usp=share_link

Very grateful for your time and guidance.

[PowerNooby]

Hi @amitchandak - thank you for responding to my post.  I tried the "last month sales" DAX without success.

Using your recommendation, the DAX is as follow

Prior Month = CALCULATE(SUM(Table1[Opportunity Value]),PREVIOUSMONTH('Date'[Date]))

There is no value in the visual.

[amitchandak]

@PowerNooby , when you use PREVIOUSMONTH , it takes the min date from the context. So if there is filter context min date in the filter. Or Min date from ROW.

If there is no date then it will take Min date from Date table.

Now is there a previous month data based row, filter or tables min date ?

Refer if needed

Why previousmonth does not give result when datesmtd is giving it: https://youtu.be/1KkoJehRVeg

[amitchandak]

@PowerNooby , Create a date table and join it with the snapshot date

example

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]))
next month Sales = CALCULATE(SUM(Sales[Sales Amount]),nextmonth('Date'[Date]))

Why Time Intelligence Fails - Powerbi 5 Savior Steps for TI :https://youtu.be/OBf0rjpp5Hw
https://amitchandak.medium.com/power-bi-5-key-points-to-make-time-intelligence-successful-bd52912a5b...
To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :radacad sqlbi My Video Series Appreciate your Kudos.