Dashboards - Dynamic content update of tiles from reports based on time

[mistophiles]

Hi,

We have our semantic layer in a SSAS multi-dimensional cube with Power BI reports connected in DirectQuery mode. The challenge we have is with pinning report elements based on time aggregations such as Month-to-date. Since there is no "time" concept on a dashboard, when a tile based on MTD time aggregation is pinned to a dashboard,

• we have to unpin it when a month changes, 
• set the filter option on the report to the current month
• repin the dashboard
• repeat for every measure that is dependent on time

The challenge is magnified when we have measures that are based on daily time aggregation such as sales by hour. These tiles need to be unpinned and repinned every day. This is not feasible.

The only workaround is to make the MDX time aggregation measures "insensitive" to time and default them to the current date. This does not seem like a good solution?

Has anyone come across this challenge and can provide help on overcoming it?

Many thanks.

Andrew

[v-caliao-msft · Accepted solution]

Hi @mistophiles,

In a SQLS erver analysis services database cube, we can caluclate MTD and YTD calculation. And in your scenario, you connect to this cube in DirectQuery mode, so calculation is not unavailable in report lelve. You need to do this in cube by using today() function. And it seems that you had done this alreadly. Here are some links for you reference.
Microsoft Analysis Services Cube: MDX Calculation for YTD,MTD and Today()
MDX Calculated Member Spanning Multiple Date Dimensions

Regards,

Charlie Liao

[mistophiles]

Thank you, Charlie. Yes, we have solved the problem using a similar approach to the what you suggested. Power BI Dashboards need some attention from the Product team as they lack the ability to use time making it cumbersome for some large enterprise customers.