cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Alienvolm Frequent Visitor
Frequent Visitor

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

Hello I'm new to Power Bi and I'm stuck on a report with very confused ideas on what and how should I do as a column or as a measure... 

 

This is my source file (I have deleted the columns that are not relevant to the issue): 

 

Capture.PNG

The file is historical and contains records from 2012. 

 

First problem: I need to calculate the number of active licenses (from the beginning of time to the last date of the previous month), where: 

  • Active = Not suspended OR Not ended
  • Service Suspended Date can be a later date than End of Last Month (hence the license is still active even if a Suspended date is present)
  • Service Suspended Date can coexist with Service End Date (Service is first suspended and then ended at a later date)
  • Service End Date can be a later date that End of Last Month (hence the license is still active even if an End date is present). 

 

I have tried to solve this with a conditional column that returns 0 if conditions are not met and Quantity if they are, but it works only if I simply consider Active the absence of Service Suspended Date and Service End Date. I tried several formulas, including SWITCH and || instead of OR but I cannot find a single one that works. 

 

How can I have an exact calculation that includes also those licenses that are set to be suspended or to end at a later date? 

 

 

Second problem: When I have the exact number of Active licenses, I need to produce a chart that shows the Total for the Active licenses MoM (i.e., not the increase/decrease by Service Start date, but whatever is Active at the end of each month. 

 

Example: 

Capture 2.PNG

2 ACCEPTED SOLUTIONS

Accepted Solutions
Super User
Super User

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

Hi @Alienvolm ,

 

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:

licenses.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Alienvolm Frequent Visitor
Frequent Visitor

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

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

 

 

 

 

View solution in original post

4 REPLIES 4
Super User
Super User

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

Hi @Alienvolm ,

 

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:

licenses.png

 

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



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




View solution in original post

Alienvolm Frequent Visitor
Frequent Visitor

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

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

 

 

 

 

View solution in original post

Super User
Super User

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

Hi @Alienvolm .

 

If you need any further assistance please tell me.

 

Regards,

MFelix



Did I answer your question? Mark my post as a solution!

Proud to be a Datanaut!




Alienvolm Frequent Visitor
Frequent Visitor

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

Hi @MFelix

 

I still need some help... 

 

For problem #1, these are the charts summarizing historical results (Active = no suspend date and no end date) and the results from the query you suggested: 

 

 

Old Calculation Chart.PNG

 

 

 

 

 

 

 

 

New Calculation Chart.PNG

 

 

 

 

 

 

 

As you can see, the new calculation outputs much smaller values, and the fact that we have 0 in 2019 cannot be right. We do have customers with active licenses, so what I think might be wrong here is that the query doesn't take into account the quantity of licences... shouldn't this be calculating a SUM (not a count) of the quantity of active licenses? 

 

On problem #2, if you see the charts on the left, what I see is the increments, not the inventory. In the charts on the right, the total column is exactly what I get as the value for the current inventory for active licenses. What I would need is a cumulative total, where each month's total = previous month + current month's increment. 

 

Example based on the first chart: 

2012 = 95

2013 = 95 + 8751 = 8846

2014 = 8846 + 28755 = 37601

2015 = 37601 + 19955 = 57556

... and so on. 

 

How I would calculate this it would be via a VAR Table that stores the previous period's results. However, I'm not sure how to do this in Power BI. 

 

Can you help? 

 

Thanks! 

Helpful resources

Announcements
Ask Amir Anything

Exclusive LIVE Community Event No. 2 – Ask Amir Anything

Next in our Triple A series: Ask Amir Netz questions about the latest updates, features and future.

Ask Amanda Anything Q&A

Ask Amanda Anything Q&A

Learn the answers to some of the questions asked during the Amanda Triple A event.

October 2019 Community Highlights

October 2019 Community Highlights

October was a busy month in the community. Read the recap article to learn about some of the events and content.

New Solution Badges

New Solution Badges

Two waves of brand new solution badges are coming! Read the article for more information on our new community badges.

Users Online
Currently online: 142 members 1,818 guests
Please welcome our newest community members: