Dynamic time: Possible to show previous month that contains data?


I am building a dashboard where one of the elements is a table of the previous month's data. This is created using the dynamic time option, which is set to show the previous month.

However, sometimes the previous month's data hasn't been aquired yet, so the entire table is blank. E.g. if the last month data we have is from July, and we just entered September, the table is empty. So I was wondering, if it is possible - dynamically - to show the previous month of which we have data from?



  • Hi Mads,

    I have an example where I created a crosstab and added a User dimension with two dynamic members: Month -2 and Month -1. These are the two columns in my screenshot:

    Then I added a visibility agent with this condition:

    allcount(all, d1, m1) = 2 and allcount(d1:0, d1, m1) = 1

    ... and made sure that the Action was to hide members of my User dimension.

    In cases where both columns contain data, the agent will hide the first column (Month -2).

    Example, opening the table in November 2022:

    In cases where only one column contains data, the agent has no effect.

    In my demo data I have data until December 2022. Simulating a date in February 2023, the Month -1 column (January 2023) will have no data, and then the Month -2 (December 2022) will become the only column visible.

    Example, opening the table in February 2023:

    BR / Ole

  • Hi Mads

    It can be done with a workaround.

    If you have a table like this - with month on the horizontal axis (the vertical axis has no importance to this example - but I'll just put Departments there as an example):

    In my table a filter with previous 12 months has been applied - but the most recent data is from August as you can see.

    Now I add a visibility agent to hide everything but the last column:

    1. choose to Hide Member:


    2. Condition should be something like this:


    3. And the action needs to be moved from the standard setting (to hide rows) to hide something on the vertical axis (in my case YMD member):


    Now you'll have the last month with data in the table:


    This is obviously a workaround - if you want to promote the idea that TARGIT should be able to autodetect the last month with data and use Dynamic Dates based on this, it would be great if you would make a feature request right here in the community: https://community.targit.com/hc/en-us/community/topics/7278561562141-Feature-requests-Ideas

  • Thanks a lot, Ole and Niels! That oughta do the trick

  • Hey Niels Thomsen.

    When I try to implement your solution it works the way you showed it. In my case, the months are on the y-axis, while the Departments are on the top (Not my decision 😅) , so I changed your formular a bit to accommodate for this: allcount(allcount(0, d-1:0, m1) > 1.   
    The above function hides all the rows (months) except the last one in the table. However, it also removes all the children (dates) from the last month, except the final day of that month. 

    Is there a way, when unfolding the remaining month, to show all the dates - and their respective data - instead of only the last day of the month? 

  • Hi Mads,

    If your time dimension is a real hierarchy, e.g., a 3-level hierarchy with Years, Months and Dates as level 1, 2 and 3 respectively, you can modify your visibility agent like this:

    • allcount(d1, d-1:0(l2), m1) > 1

    In this way I am forcing the count calculation to work on level 2 (l2), the Month level.

    If you just put together two dimensions, Months and Dates, on the vertical axis, you may need to do something like this instead:

    • allcount(d1, d-1:0(l(1,0)), m1) > 1

    BR / Ole


Please sign in to leave a comment.

Didn't find what you were looking for?

New post