Monthly Summary Table

[Masikonde]

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.

[v-joesh-msft · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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 · Accepted solution]

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/EQm1f8bg8ytHkVoVIp...

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.