Using DAX to calculate active Salesforce assets

Question

Hi all -

I'm working on an analysis in Power BI and simultaneously going through the online learning modules: https://learn.microsoft.com/en-us/learn/paths/dax-power-bi/

The challenge I'm hitting (and that's not covered in the modules so far) is how to take data from a summary table and calculate counts in a time series, inside a Matrix visualization.

• The columns will be months from my date hierarchy.
• The measure I want to create would query a Salesforce Assets table (imported into the model), and count the number of assets "in service" for each monthly column.
• Each Asset record has an "Accepted Date" and an "Uninstalled Date." "In service" is defined as any dates between Accepted and Uninstalled, or if there's a value in Accepted and Uninstalled is null (e.g. the asset is still in service today)

Getting this measure sorted out is key to completing several analyses for my group; I feel confident that when I get the syntax on this one, I can riff on it for derivative measures (e.g. calculate monthly Cases as a ratio of In Service assets). Hope the question is clear, and I'll keep researching in parallel. Thanks!

Answer 1

This is a Power BI question, not a Power Query one. Please post this over on the Power BI Community site.