Forum Discussion

New Member

2 years ago

Periodicity based calculation (measure) in PowerBI

Hi I have to datasets: Calendar of future dates for the next 2 years Active subscriptions database with the following clolumns: Subcription_ID, Product_Name, Period_in_Months and Valid_From_Date ...

Data Modeling

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.

Periodicity based calculation (measure) in PowerBI.pbix68 KB

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: