Complete date series

[Anonymous]

Hi a Newcomer to DAX and PBI here,

i want to "complete" a date series in my dataset.

The following scenario: I feed a data cube from the server into my PBI desktop application. However, this cube only contains date entries for days with values; no value and the day does not even appear.

Example:

The columns "Dim_company", "Dim_division", "Dim_kpi", "Dim_version" are group variables, each of these groups tracks its own time series.

The snippet now shows the example company "1234" with its divisions "ABC" and "DFG". The problem now is that for the division "DFG" no value was delivered on October 2nd 2021 and the line is therefore missing.

What I would like to do now using DAX is to automatically insert a blank row for the missing date, so that in my example the two divisions cover the same days. ( The columns have shifted compared to the first screenshot, but I hope you still get what I mean )

For those of you who are also familiar with R - In R I would solve the problem like this:

library(dplyr)
library(tidyverse)

data  %>% group_by(Dim_company,Dim_currency,Dim_division,Dim_kpi,Dim_version,Dim_wildcard_dc) %>%
          complete(Dim_period_daily2 = seq.Date(min(Dim_period_daily2), max(Dim_period_daily2), by = "day")) %>%
          ungroup()

[tamerj1]

Hi @Anonymous 

what could be the purpose of having this blank row?

[Anonymous]

Hi @tamerj1 ,

the individual values are cumulated in a rolling sum for each group over the days each month. After that the series are supposed to be visualized in a simple graph (for now).

The visualization of a single group (like just "ABC") works fine, but when you pick multiple group the cumulative values are supposed to be added together and displayed. 

This works fine as long as the two groups have the same days within each month.

The summation is dynamic enough to take the last cumulative value if there is a blank.

But if there is an entry missing one element gets skipped which results in this drops in the graph (s. below):