Forum Discussion

Masikonde's avatar
Masikonde
Frequent Visitor
6 years ago
Solved

Monthly Summary Table

Hello Everyone,

 

I have built a summary table on all reports for the Executive to get a quick visual of what happened for the month. In the report attached the "Monthly Report Summary" of Days to Close is the avarage number of days it took each department to close its invoices.  The Dax measure formula I use to get the summary is: 

 
Days to Close = IF(ISBLANK(SELECTEDVALUE(Days2Close[Days2Close])),"No Data",SELECTEDVALUE(Days2Close[Days2Close]))
In the previous months when I or anyone clicked on any month on the "Line Chart" I would get the Monthly Summary table to reflect that month selected. After installing the recent updates I lost that capability. Does anyone have an idea of how I can get that table to reflect the summary of the selected month?
 
I'm open for other summary visualization suggestions. 
 
Please see the picture below.
 
Need Help
  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Masikonde ,

    I think "No Data" is displayed in Marketing because there are multiple Marketings in September. You can change the formula in Days to Close to the following:

    Days to Close = IF(ISBLANK(SUM(Days2Close[Days2Close])),"No Data",SUM(Days2Close[Days2Close]))

    Best Regards,

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

  • Masikonde's avatar
    Masikonde
    6 years ago
    Days to Close = IF(ISBLANK(SUM(Days2Close[Days2Close])),"No Data",SUM(Days2Close[Days2Close]))

    The above DAX formula does gives the ability to show Days to Close for each month selected, but at default it shows sum of all months. Is there way to set the DAX to default to current month?

  • v-joesh-msft's avatar
    v-joesh-msft
    6 years ago

    Hi Masikonde ,

    You can try the following measure:

    Days to Close = 
var __MAXDATE=CALCULATE(MAX('Days2Close'[Date]),ALL('Days2Close'))
var __measure= IF(ISBLANK(SUM('Days2Close'[Days2Close])),"No Data",SUM('Days2Close'[Days2Close]))
var __measure2 = IF(ISBLANK(CALCULATE(SUM('Days2Close'[Days2Close]),FILTER('Days2Close','Days2Close'[Date]=__MAXDATE))),"No Data",CALCULATE(SUM('Days2Close'[Days2Close]),FILTER('Days2Close','Days2Close'[Date]=__MAXDATE)))
return IF(HASONEVALUE('Days2Close'[Date]),__measure,__measure2)

    Here is a demo, please try it:

    https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQm1f8bg8ytHkVoVIpdhn7EBaYoL_NVi9yCt7aVyvxTU7A?e=IfZ7gX

    Best Regards,

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

     

3 Replies

  • v-joesh-msft's avatar
    v-joesh-msft
    Solution Sage
    6 years ago

    Hi Masikonde ,

    I think "No Data" is displayed in Marketing because there are multiple Marketings in September. You can change the formula in Days to Close to the following:

    Days to Close = IF(ISBLANK(SUM(Days2Close[Days2Close])),"No Data",SUM(Days2Close[Days2Close]))

    Best Regards,

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

    • Masikonde's avatar
      Masikonde
      Frequent Visitor
      6 years ago
      Days to Close = IF(ISBLANK(SUM(Days2Close[Days2Close])),"No Data",SUM(Days2Close[Days2Close]))

      The above DAX formula does gives the ability to show Days to Close for each month selected, but at default it shows sum of all months. Is there way to set the DAX to default to current month?

      • v-joesh-msft's avatar
        v-joesh-msft
        Solution Sage
        6 years ago

        Hi Masikonde ,

        You can try the following measure:

        Days to Close = 
var __MAXDATE=CALCULATE(MAX('Days2Close'[Date]),ALL('Days2Close'))
var __measure= IF(ISBLANK(SUM('Days2Close'[Days2Close])),"No Data",SUM('Days2Close'[Days2Close]))
var __measure2 = IF(ISBLANK(CALCULATE(SUM('Days2Close'[Days2Close]),FILTER('Days2Close','Days2Close'[Date]=__MAXDATE))),"No Data",CALCULATE(SUM('Days2Close'[Days2Close]),FILTER('Days2Close','Days2Close'[Date]=__MAXDATE)))
return IF(HASONEVALUE('Days2Close'[Date]),__measure,__measure2)

        Here is a demo, please try it:

        https://qiuyunus-my.sharepoint.com/:u:/g/personal/pbipro_qiuyunus_onmicrosoft_com/EQm1f8bg8ytHkVoVIpdhn7EBaYoL_NVi9yCt7aVyvxTU7A?e=IfZ7gX

        Best Regards,

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