Two Queries on Report

[stuieb]

Hi

I'm coming here, as I'm losing my mind with PowerBI.

I've come from a background, transforming datasets with SQL, C# and Excel.

What seemed like an elementary report to  produce as more foray into PowerBI is driving me insane and is frustrating me.

Could somone lend some advice, I feel like I'm missing a concept here that's making this more difficult than it should be.

For this report I have a table of Salesforce opportunities, amongst other fields, where have a date that a deal was called and another field containing the date that the deal crashed or fell through.  I'd like to chart the count of deals that were made in a month along with the number of deals that crashed in that same month.  Previously I would have written two queries in SQL, one that counted deals and grouped by the month the deal was made and another counting the deals but grouping by the month they crashed, I'd then join these two queries by that month.  I can't do this in SQL as it's importing from Salesforce directly to PowerBI.

Could some provide some advice or suggest a resource that might help?

Thanks

[v-xicai · Accepted solution]

Hi @stuieb ,

You may create a calendar table using DAX below, then create relationship between the two tables on date field, assuming it is the [deal made date] for your fact table.

Calendar=CALENDARAUTO()

Then you may create measures like DAX below.

Count for made date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal made date])= MONTH(MAX(Table1[deal made date]))))
 
Count for crashed date=CALCULATE(COUNT(Table1[Deal]), FILTER(ALLSELECTED(Table1), MONTH(Table1[deal crashed date])= MONTH(MAX(Table1[deal crashed date]))), USERELATIONSHIP(Table1[deal crashed date], Calendar[Date])

You may choose Clustered column chart to display the data, put Calendar[Date] into Axis box, and put the two new created measures above into Value box.

If I misunderstood it, could you please share your sample data or desired output screenshots for further analysis, you can also upload sample pbix to OneDrive and post the link here. Do mask sensitive data before uploading.

Best Regards,

Amy

Community Support Team _ Amy

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