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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
athgeor
Frequent Visitor

DAX Comparing counts from two different snapshots

Hi all,

 

This should be quite straightforward, but I can't seem to be able to debug it yet... 

I have a series of data called 'Opportunities' that are recorded in an excel spreadsheet.

 

Opportunities
Opportunity 1
Opportunity 2
Opportunity 3
Opportunity 4 ...

 

Every week, we capture a snapshot of the list and assign a date in the 'Date Captured' column.

 

Date CapturedOpportunitiesProjects
10/02/2022Opportunity 1Opportunity
10/02/2022Opportunity 2Opportunity
10/02/2022Opportunity 3Opportunity
17/02/2022Opportunity 1Opportunity
17/02/2022Opportunity 2Opportunity
17/02/2022Opportunity 3Opportunity
17/02/2022Opportunity 4Opportunity
24/02/2022Opportunity 1Opportunity
24/02/2022Opportunity 2Opportunity
24/02/2022Opportunity 3Opportunity
24/02/2022Opportunity 4Opportunity
24/02/2022Opportunity 5Opportunity

 

What I would like to have is a summary table with the latest number of opportunities (5), a dynamic table that shows the difference between the current/latest number of Opportunities and a previous date (selected using a slicer of the 'Date Captured'), and a table that shows the number of Opps for that previous date (3).

 

Latest DateCount
24/02/2022 5

 

Difference

2

 

Earlier DateCount
10/02/20223

 

I have written the following codes, but I keep getting 0s in the middle table (difference). Any help?

 

Step 1: 

Opportunties_Count = CALCULATE (COUNT('Table'[Projects]),

FILTER('Table',[Projects]="Opportunity"))
 
Step 2: 
LatestDateCount =
VAR LatestDate= MAX ('Table'[Date Captured] )
VAR LatestDateCount= CALCULATE ( [Opportunities_Count],'Table'[Date Captured] = LatestDate)
RETURN CALCULATE(LatestDateCount)
 
Step 3:
EarlierDateCount =
VAR EarlierDate= SELECTEDVALUE('Table'[Date Captured] )
VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],'Table'[Date Captured] = EarlierDate)
RETURN CALCULATE(EarlierDateCount)
 
Step 4:
Difference = LatestDateCount - EarlierDateCount
 
Please note that I do get the right values in the top and bottom tables. The only one that doesn't seem to work is the Difference table. 

 

1 ACCEPTED SOLUTION
ERD
Super User
Super User

Hi @athgeor ,

I'm not sure what you want to achieve:

Do you want to compare the values between the LATEST day available in a table and a day you choose in a slicer?

If so:

LatestDateCount =
VAR LatestDate = MAXX ( ALL ( 'Table' ), 'Table'[Date Captured] )
VAR LatestDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = LatestDate )
RETURN
    LatestDateCount
EarlierDateCount =
VAR EarlierDate = SELECTEDVALUE ( 'Table'[Date Captured] )
VAR EarlierDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = EarlierDate )
RETURN
    EarlierDateCount

ERD_0-1645619305069.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

View solution in original post

4 REPLIES 4
athgeor
Frequent Visitor

Thanks @ERD! That works exactly as I wanted. Seems like the main difference between my code and yours was the MAX vs MAXX. Good lesson learned!

ERD
Super User
Super User

Hi @athgeor ,

I'm not sure what you want to achieve:

Do you want to compare the values between the LATEST day available in a table and a day you choose in a slicer?

If so:

LatestDateCount =
VAR LatestDate = MAXX ( ALL ( 'Table' ), 'Table'[Date Captured] )
VAR LatestDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = LatestDate )
RETURN
    LatestDateCount
EarlierDateCount =
VAR EarlierDate = SELECTEDVALUE ( 'Table'[Date Captured] )
VAR EarlierDateCount = CALCULATE ( [Opportunties_Count], 'Table'[Date Captured] = EarlierDate )
RETURN
    EarlierDateCount

ERD_0-1645619305069.png

If this post helps, then please consider Accept it as the solution ✔️to help the other members find it more quickly.

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly. Appreciate your Kudos.

Check out my latest demo report in the data story gallery.

Stand with Ukraine!


Here are official ways you can support Ukraine financially (accounts with multiple currencies):
1) Support the Armed Forces of Ukraine: https://bank.gov.ua/ua/about/support-the-armed-forces
2) Come Back Alive foundation: https://www.comebackalive.in.ua/

Thank you!

athgeor
Frequent Visitor

Many thanks @amitchandak 

Your suggestion seems to work in principle, but doesn't seem to work when I use a slicer for the past date. 

In such case, the middle comparison table gets the previous date values, and the bottom table (previous date values) becomes blank.

 

Not sure what's the problem...

 

amitchandak
Super User
Super User

@athgeor , Try like

 

Opportunties_Count = CALCULATE (COUNT('Table'[Projects]),
FILTER('Table',[Projects]="Opportunity"))

Step 3:
LatestDateCount =
VAR LatestDate= MAXX (allseleceted('Table'), 'Table'[Date Captured] ) //or max('Table'[Date Captured])
VAR LatestDateCount= CALCULATE ( [Opportunities_Count],filter('Table', 'Table'[Date Captured] = LatestDate) )
RETURN CALCULATE(LatestDateCount)

Step 3:
EarlierDateCount =
VAR LatestDate= MAXX (allseleceted('Table'), 'Table'[Date Captured] )
VAR EarlierDate= MAXX (filter(allseleceted('Table'), 'Table'[Date Captured] < LatestDate), 'Table'[Date Captured] )
VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],filter('Table', 'Table'[Date Captured] = EarlierDate) )
RETURN CALCULATE(EarlierDateCount)

Step 4:
Difference = LatestDateCount - EarlierDateCount

 

 

In case you want select a date and compare. better to have separate table date table, because you need all for EarlierDateCount

 

EarlierDateCount =
VAR LatestDate= MAXX (allseleceted('Date'), 'Date'[Date] )
VAR EarlierDate= calculate(MAX('Table'[Date Captured] ),filter(all('Date'), 'Date'[Date] < LatestDate))
VAR EarlierDateCount= CALCULATE ( [Opportunities_Count],filter(all('Date'), 'Date'[Date] = EarlierDate) )
RETURN CALCULATE(EarlierDateCount)

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors