calculate in which month it is available

[NvdV1986]

Hi there,

I really can use your help:

I want to link invoice data in Power BI to whether or not someone is still using the product. Apps can be activated, and we record the activation date. Of course, apps can also be deactivated, and we record that date as well.

We invoice monthly, so I need to be able to see for each month whether it should be invoiced. I then compare this to the invoices that were actually sent. But how do I calculate which months should have an invoice?

I have a table with the following data:

From revenue, I have the invoice date and product name.

From a log, I have the name of the installed app, activation date, and deactivation date.

So in the end I have a table with products in rows and year/month as colums. The value is the amount invoices that month per product. Next to the amount I need to see if the app was on. 

[V-yubandi-msft]

Hi @NvdV1986 ,
Could you let me know if your issue has been resolved or if you still need any more information? If you need further help, please let us know.

[NvdV1986]

hi hi,

I did not yet had the time to check. As soon as I did, i'll let you know.

[V-yubandi-msft]

Thanks for your response. Could you please let us know the ETA by when you will be able to test the provided solution? This will help us to know when we can follow up. If you encounter any challenges during implementing the provided solution, please inform us and we’ll be glad to assist.

[NvdV1986]

There is changing something but not what we want. 

I can’t combine the data from the two tables. That is, of course, the goal. But I think that’s because of the second point.

The data doesn’t match up. And when I filter by a customer, the table with installed apps doesn’t change. I think this happens because I no longer have a relationship between Installed Apps and Brands. Power BI also no longer accepts it due to an ambiguous path. However, I can’t do without a link between Brands and Installed Apps.

The bridge table is almost not an option, because it would mean that every time we add a new product, we’d have to update it manually. That would easily fall through the cracks. Is there a way to automate that table — so that it pulls all product codes from my product list and adds them to the bridge table? And where should this table sit within the relationships?

After Friday, I’ll be on vacation for two weeks, so I won’t be able to test it until after that.

[V-yubandi-msft]

Hi @NvdV1986 ,

Thank you for explaining everything so clearly. I understand why this situation has been frustrating. The issue with Installed Apps not responding to customer filters is probably due to ambiguous paths in your data model, and I agree that manually updating a bridge table isn’t ideal.

The solution is to create a dynamic bridge table in Power Query, which will automatically include all products and months from your current tables. This way, it stays up to date without manual effort.

After creating the table, connect it as follows

ProductMonthBridge[ProductID] to ProductList[ProductID]
ProductMonthBridge[Month] to DateTable[Month]

Use this bridge as the foundation for your matrix visual. This approach avoids circular relationships and allows you to combine invoice data with app activity, while ensuring customer and brand filters work correctly.

I hope this clears things up and I really appreciate your patience in reviewing everything.

[NvdV1986]

Hi there, 

Back from annual leave and tried to build this but I can't sort it out. 

Is it okay for you if I rebuild my original report with the real tables but fake data so we can try to build this with the real structure?

[NvdV1986]

Hi,

I've build a dummy data file. So hopefully you can help me.

https://drive.google.com/drive/folders/1LJWRNH5tGPx_MBIIaVPHAWiEVjSrXwk1?usp=sharing

Can you check?

Thank you!

[V-yubandi-msft]

Hello @NvdV1986 ,

Thanks for waiting.  Based on your requirement to identify which months an app should be invoiced (using activation and deactivation dates) and compare that with the actual invoices, this can be fully achieved in Power BI using standard DAX.

FYI:

I’ve attached a sample .pbix file for your reference  please review it for more details.

Hope this meets your expectations.

Thanks.

[NvdV1986]

Hi @V-yubandi-msft,

I looked at your file but it's not working when I implement it into my file. The data structure is different. I need to make a connection between my installed apps table and my mappingProducts table because in the table with installed apps the product ID is very different then how we communicate it to our client.

My mappingproducts is connected to my invoice details. But when I only put the name of the app (frrom table mapping products) and YearMonth from Appmonth, i get an error. 

Is it possible to make an example in the dummy file I send before?

https://drive.google.com/drive/folders/1LJWRNH5tGPx_MBIIaVPHAWiEVjSrXwk1?usp=sharing

[V-yubandi-msft]

Hi @NvdV1986 , did you get a chance to review my latest response? Please let me know if it meets your requirement or if any changes are needed.

Thank You.

[NvdV1986]

Hi @V-yubandi-msft , not yet. I hope to look at it this week.

[NvdV1986]

HI there, thanks for this. I can't test this today so I will test is when I'm back from annual leave. I hope the first days of november. 

[NvdV1986]

Hereby the rigth URL: https://drive.google.com/file/d/1ygO7_m5_AKU4N93qapKVCdjMEf_J3z4r/view?usp=sharing

[danextian]

Hi @NvdV1986 

It is unclear what your expected result is as you did not provide that, just the sample data -what values do you expect to see in your matrix and why?  How to compute for those values?

That aside, check if the attached is what you're looking for. Otherwise, please provide the details being asked above.

[NvdV1986]

So many thanks. I visualized the outcome in an Excel. I wrote it in the test file but this is better:
Basicly he show de invoicing per month and if the app was on in 1 view. Does this help?

[danextian]

There is no invoiced amount for product a in your data for December? How did you arrive to that number? Can you please use your sample data if you did not?

[NvdV1986]

That's correct. But you solution works. 

I've put the invoice data next to it and it gives me what I need. So i'm going to try and build it into my real report. 

The only thing I would still really like is for the cells to turn red if something is incorrect. So:

If there is an amount but the app was not active in that month, then both cells should turn red.

If there is no amount but the app was active, then both cells should turn red.

[danextian]

Create a measure to be used as conditional formatting field value

Amount color fill = 
VAR _amount =
    NOT ( ISBLANK ( SUM ( Invoiced[Amount] ) ) )
VAR _active =
    NOT ( ISBLANK ( [active?] ) )
RETURN
    SWITCH (
        TRUE (),
        _amount && NOT _active, "#EFB5B9",
        NOT _amount && _active, "#EFB5B9"
    )

[NvdV1986]

Thank you very much. I'm going to test it in my real data tomorrow! You made me very happy!.

[NvdV1986]

I Tried to put it in my real data but I see that I can't make the connection with products in the right way. My developers are going to arrange that I get the right data. It will take a week so I'll let you know if it works later.