Inventory over time: Multiple IFS issue and MoM/YoY visualization

Hi Anonymous ,

Although your data table is reduced try to do the following:

• Create a date table (unrelated with the other table)
• Create the following 3 measures:

Active Licenses =
CALCULATE (
    COUNT ( Services[Product Description] );
    FILTER (
        ALL (
            Services[Product Description];
            Services[Service Start Date];
            Services[Service End Date];
            Services[Service Suspended Date]
        );
        Services[Service Start Date] <= MAX ( 'Calendar'[Date] )
            && Services[Service Start Date] >= MIN ( 'Calendar'[Date] )
            && ( Services[Service End Date] >= MAX ( 'Calendar'[Date] ) )
    )
) + 0


Ended Licenses =
CALCULATE (
    COUNT ( Services[Product Description] );
    FILTER (
        ALL (
            Services[Product Description];
            Services[Service Start Date];
            Services[Service End Date];
            Services[Service Suspended Date]
        );
        (
            Services[Service End Date] <= MAX ( 'Calendar'[Date] )
                && Services[Service End Date] >= MIN ( 'Calendar'[Date] )
        )
    )
) + 0

Suspended Licenses = 
CALCULATE (
    COUNT ( Services[Product Description] );
    FILTER (
        ALL (
            Services[Product Description];
            Services[Service Start Date];
            Services[Service End Date];
            Services[Service Suspended Date]
        );

                     Services[Service Suspended Date] >= min ( 'Calendar'[Date] )
           &&  Services[Service Suspended Date] <= max ( 'Calendar'[Date] )
    )
) + 0

This should give the result below:

Believe this result is correct considering your data, can you please confirm?

Regarding your second question what do you need exactly what is the calculation should look like? In this case you want to have the change from 2 to zero?

Check PBIX.

Regards,

MFelix

Hi MFelix, thanks a million for the scripts and the file!

I implemented them and they seem to work fine. However, the result on my Inventory file doesn't seem to match the numbers from the previous reports (the number of active licenses I obtain with your solution is considerably lower than what we have historically for the same periods). 

I will try to dig into this and try to understand what’s wrong. We know that the calculation for the previous files was made just based on “Active = no suspend date and no end date”, which is imprecise. But the difference now is significant. 

For the second question, I need to be able to see the inventory of active licenses at specific checkpoints (end of each month and end of each year), but if I well understand what you’ve done, this is already in the output of that table. 

No issues with the calculation of the variance between periods. 

Thanks again! This has been very helpful! 

Simona