Forum Discussion

tomashavlicek's avatar
tomashavlicek
New Member
2 years ago

Periodicity based calculation (measure) in PowerBI

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.

 

 

Data Modeling

2 Replies

  • Anonymous's avatar
    Anonymous
    Not applicable
    2 years ago

    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's avatar
      tomashavlicek
      New Member
      2 years ago

      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.