Expand periodic data or create DAX formula

Hi!

I have the source data as attached in the example. It is for a subscription service e.g. a magazine. The customer can decide to pay monthly, semi-annual or annually. The start month indicates when the amount is to be payed. For monthly fees it will be every month and for semi annually it will be the start month and start month + 6 eg march and September as in the example. I need to create a report that indicates the amount that will be collected from subscriptions each month based on this data. See example on the right. Would this be possible with DAX or do I need to expand the data in some way to be able to create this? All the amounts, periodicity etc are in one table and I of course have a standard date table.

Thanks
FredrikSubscription sample.pbix (183.2 KB)

Hi @Frede. I don’t think your example got attached properly I can’t see it anyway). If your could please re-attach, and also provide a work-in-progress PBIX (with sanitized date, if necessary) and a mock-up of your desired outcome, that’d be great.
Greg

Sorry, forgot to attach the image. It’s in the original post now.

One of DAX or Power Query (or perhaps both) can get you there I’m sure. Can you please provide your work-in-progress PBIX for the forum members to see how far you’ve gotten?
Greg

Attached a sample PBIX. Don’t know how much it will help though.

Thanks for posting your question @Frede. To receive a resolution in a timely manner please make sure that you provide all the necessary details on this thread.

Here is a potential list of additional information to include in this thread; demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

Including all of the above will likely enable a quick solution to your question.

Hello @Frede,

Thank You for posting your query onto the Forum.

Firstly, the nature of the data itself is so small nothing conclusive can be said. If you’ve only these many clients and the collection of the months are fixed i.e. if you collect your semi - annually subscription amount in the month of March and annual subscription amount in the month of June then you can use the formula provided below for the reference to achieve the output as you’ve shown above -

Subscription Amount = 
SWITCH( TRUE() , 
    SELECTEDVALUE( Calender[MonthLong] ) = "Mars" , 1000 + 100 , 
    SELECTEDVALUE( Calender[MonthLong] ) = "September" , 1000 + 100 , 
    SELECTEDVALUE( Calender[MonthLong] ) = "Juni" , 2000 + 100 , 
100 )

Below is the screenshot of the result provided for the reference -

And to have the correct grand totals. Below is the formula as well as screenshot of the result provided for the reference -

Total Subscription Amount = 
SUMX(
    SUMMARIZE(
        Calender ,
        Calender[Year] , 
        Calender[MonthLong] , 
        "Totals" , 
        [Subscription Amount] ) , 
    [Totals]
)

Please Note:

  1. This solution is provided keeping in mind you’ve these many clients and collection months are fixed. And also keeping in view of the consideration the output that you’re looking for (As provided/mentioned in the first post). If that’s not case, then please expand your data and specify the scenario alongwith the result that you’re looking for.

  2. Also the language used in subscription table is different than the language used in the date table. Please maintain consistency throughout the data model.

Thanks and Warm Regards,
Harsh

Subscription - Harsh.pbix (66.4 KB)

Hi!

Let me expand on my first example and maybe it will be clearer.
Customers can sign up at any time during the year of course, and they can choose their payment periodicity (annually, monthly etc.) themselves. There are thousands of customers.

I have also added to the example the subscription start and end date to illustrate the example further but it is not really needed as I have a working way of determining whether a subscription is valid during a specific period. I want the user to be able to use slicer to select the period they want to view this data over and that should only filter on active subscriptions i.e. where the dates in the slicer are within the subscription period. The report only need to contain columns H and I, the rest is just for your reference to understand how the values should be summarized. Also attached a new PBIX with the new sample data.Subscription sample.pbix (186.0 KB)

Hi @Frede,

Can you see if this works for you.

I created records for all intermittent dates with a custom function called: fxCreateDateRange

IMPORTANT please note that this functions creates dates up to the end of your Date table (CalenderMax) or subscription enddate (endDate) - whatever comes first.

( myStartMonth as number, myStartYear as number, myPeriodID as text, endDate as any ) as list =>
    let
        BufferedInterval = Table.Buffer(Interval),
        maxDate = if endDate <> Date.Type or endDate = null then Date.From(CalenderMax) else List.Min( { Date.From(CalenderMax), Date.From(endDate) }),
        Source = List.Generate(
                () => [x = 1, y = maxDate, z = #date(myStartYear, myStartMonth, 1)], 
                each [z] < [y], 
                each [x = [x] + 1, y = [y], z = Date.AddMonths( [z], 
                    Table.SelectRows( BufferedInterval, 
                        (BT) => BT[Periodicity] = myPeriodID ){0}[Interval in Months]
                    )
                ]
            ),
        CreateTable = Table.FromRecords(Source)
    in
        try CreateTable[z] otherwise {}

With this result.

Next I created this measure:

Total Amount = 
CALCULATE(
    SUM( Subscriptions[Amount] ),
    FILTER( VALUES( Subscriptions[YearMonth] ),
        Subscriptions[YearMonth] IN VALUES( Calender[YearMonth] )
    )
)

Placed in a visual with a slicer on the page it looks like this.

image

Here’s your sample file. eDNA - Subscription sample.pbix (201.4 KB)
I hope this is helpful

4 Likes

Hi @Frede, a response on this post has been tagged as “Solution”. If you have a follow question or concern related to this topic, please remove the Solution tag first by clicking the three dots beside Reply and then untick the check box. Also, we’ve recently launched the Enterprise DNA Forum User Experience Survey, please feel free to answer it and give your insights on how we can further improve the Support forum. Thanks!

Wow @Melissa, that is a super-thought. I would not have thought of that myself. I will check this out for sure. Many thanks!
/Frede

@Melissa Could you please elaborate and explain in more detail the function created, especially the list.generate part. I have a hard time intetpreting exactly what it does.

/Frede

Hi @Frede,

I’ve added comments to the M code below
I hope this provides enough detail to help you better understand what is going on in List.Generate

( myStartMonth as number, myStartYear as number, myPeriodID as text, endDate as any ) as list =>
    let
        // load the Interval table into memory
        BufferedInterval = Table.Buffer(Interval),
        // keep the min date from the last date in the date table and the subscription enddate 
        maxDate = if endDate <> Date.Type or endDate = null then Date.From(CalenderMax) else List.Min( { Date.From(CalenderMax), Date.From(endDate) }),
        Source = List.Generate(
                // Create an initial Record
                () => [x = 1, y = maxDate, z = #date(myStartYear, myStartMonth, 1)], 
                // Define the Condition (Do-While-Loop)
                each [z] < [y], 
                // Function that defines a new value for each Record element in that iteration.
                each [x = [x] + 1, y = [y], z = Date.AddMonths( [z], 
                    Table.SelectRows( BufferedInterval, 
                        (BT) => BT[Periodicity] = myPeriodID ){0}[Interval in Months]
                    )
                ]
            ),
        // Create a table from the generated Record values in List.Generate
        CreateTable = Table.FromRecords(Source)
    in
        // from that table extract the values from column "z", a single column returns a list
        // if this evaluation returns an error an empty list is returned
        try CreateTable[z] otherwise {}
2 Likes

I’ve tried this in practice now and it works beautifully. Many thanks for such an elegant solution.