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): 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 endedService 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:

Anonymous

Not applicable

7 years ago

Solved

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):

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:

Need Help

MFelix

7 years ago

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

Licenses.pbix35 KB

Anonymous
7 years ago
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

4 Replies

MFelix
Super User
7 years ago
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
Licenses.pbix35 KB
- Anonymous
  Not applicable
  7 years ago
  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
  - MFelix
    Super User
    7 years ago
    Hi Anonymous .
    
    If you need any further assistance please tell me.
    
    Regards,
    
    MFelix

Forum Discussion

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

4 Replies

Related content

Inventory and Stock balance calculation

Inventory Forecast

Inventory Management Dashboard

Inventory opening closing

How to show multiple metrics/measures in a visual?