Forum Discussion

stuieb's avatar
stuieb
Frequent Visitor
6 years ago
Solved

Two Queries on Report

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 mor...
Need Help
  • v-xicai's avatar
    v-xicai
    6 years ago

    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.

v-xicai's avatar
v-xicai
Community Support
6 years ago

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.