Periodicity based calculation (measure) in PowerBI

[tomashavlicek]

Hi I have to datasets:

1. Calendar of future dates for the next 2 years
2. Active subscriptions database with the following clolumns: Subcription_ID, Product_Name, Period_in_Months and Valid_From_Date

So each subsription has a starting date (Valid_From_Date) and based on period (e.g. 2 months) the product will ship as follows:

1st shipment on Valid_From_Date, 2nd shipment on Valid_From_Date + (1 * 2 months), 3rd shipment on Valid_From_Date + (2 * 2 months), 4th shipment on Valid_From_Date + (3 * 2 months), etc.

What I would like to do is a Measure, that would caluclate the number of shipments scheduled for each date in the calendar based on this dataset so I can plot she upcoming shipments for example like this:

I think the trick is in calculating the number of days between the given date and the Valid From date and dividing the result by periodicity in days. If the result of the division is whole number, the shipment is scheduled on that date.

Have you ever tried bulding a mesure like this? I wanted a simple solution instead of building new datasets with 100 of upcoming shipment days for thousands of customers.

Many thanks,

Tomas.

[v-rzhou-msft]

Hi @tomashavlicek ,

My sample:

Calendar =
ADDCOLUMNS (
    CALENDAR ( EOMONTH ( TODAY (), -1 ) + 1, EOMONTH ( TODAY (), 24 ) ),
    "Year", YEAR ( [Date] ),
    "Month", FORMAT ( [Date], "MMM" ),
    "MonthSort", MONTH ( [Date] )
)

Measure:

Measure = 
VAR _DATEDIFF = DATEDIFF(MAX('Table'[Valid_From_Date]),MAX('Calendar'[Date]),MONTH)
VAR _MOD = MOD(_DATEDIFF,SUM('Table'[Month Interval]))
RETURN
CALCULATE(SUM('Table'[Quantity]),FILTER('Table',_DATEDIFF>=0 && _MOD = 0))

Result is as below.

Best Regards,
Rico Zhou

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

[tomashavlicek]

Hi, thank you very much for the quick reply. I tried the solution but struggle to implement it.

I have:

- created a new calendar based on your suggenstion

- created a new Measure (Quantity MOD) in my table of subsciptions (BC_Subscriptions)

- created a blank page with a new visual to plot this

However the output look like this (dates are fine but there is data only in the first month correponding to the total amount of subcriptions in the table:

The measue:

The BC Subscriptions table:

Calendar table:

Any relations I should make between the tables?

Thank you very much.

Tomas.