Issue with data summing up to month/year from product level

[kuczkrz]

Hi all,

I have a a simple fact query as follows. Netto is a fact measure set up to summarize as SUM.

All I need to do is to create a simple calculations to present MRRs

I was able to complete CurrentMonth, LastMonth, Exisiting and New values with calculations as follows:

CurrentMonth = SUM('wFirma InvoiceContents'[Netto])

LastMonth = CALCULATE(SUM('wFirma InvoiceContents'[Netto]), PARALLELPERIOD('Daty'[Date], -1,MONTH))

Existing = IF ( 'wFirma InvoiceContents'[LastMonth] > 0,  IF ( 'wFirma InvoiceContents'[CurrentMonth] < 'wFirma InvoiceContents'[LastMonth], 'wFirma InvoiceContents'[CurrentMonth], 'wFirma InvoiceContents'[LastMonth] ) )

New = CALCULATE( SUM('wFirma InvoiceContents'[Netto]) , FILTER('wFirma InvoiceContents', 'wFirma InvoiceContents'[IsFirstOfThisGoodForContractor] = TRUE() ) )

Upgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] < [CurrentMonth], [CurrentMonth] - [LastMonth] ) ) )

Downgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0, (IF ( [LastMonth] > [CurrentMonth], [CurrentMonth] - [LastMonth] ) ) )

Reactivations =  IF ('wFirma InvoiceContents'[LastMonth] = 0 && 'wFirma InvoiceContents'[CurrentMonth] > 0, CALCULATE(SUM('wFirma InvoiceContents'[Netto]) , FILTER('wFirma InvoiceContents', 'wFirma InvoiceContents'[IsFirstOfThisGoodForContractor] = FALSE() )))

Churn = IF ([LastMonth] > 0 && [CurrentMonth] = 0 , [CurrentMonth] - [LastMonth] )

I'm summarizing this data for year & month to present these in last 24 months.

I have an issue with summing up this values for month. Please find below what I get. 

I'd like to present data for example as follows:

for grudzień (december) 2018

Exisitng = 3000 

New = 0

Reactivations = 0

Upgrades = 0

Downgrades = 0 

Churn = -4400

For styczeń (January) 2019

Exisitng = 3000

New = 0

Reactivations = 4400

Upgrades = 0

Downgrades = 0 

Churn = 0

For luty (february) 2019

Existing = 7400

New = 0

Reactivations = 1500

Upgrades = 1860

Downgrades = -925 

Churn = 0

Everything is good on a product level, but going up, to month level/year level, some data are presented differently. I know my formulas are causing this issue with Downgrades/Upgrades, Reactivations and Churn, but at this point I do not have a clue how to create these calculations. Could you please help me out, or give me a hint how to start to get this?

Any help will be much appreciated.

Thank you

 

[Ashish_Mathur]

Hi,

Could you share a PowerBI Desktop file to work with.  Please ensure data is in English.  Also, share a small dataset so that figures can be easily verified.  Kinldy also show your expected result.

[kuczkrz]

Hi Ashish,

I have created a sample model. Please find it here 

What I'm trying to show here are some calculations like

- Existing - if a Contractor had specific good at specific price last month, and has this good in current period, then this value is existing

New - if a Contractor is buying a good for the first time (depend this on a IsfirstOfThisGoodForContractor attribute)

Upgrade - if a Contractor is buying more of a good in current month than last month

Downgrade - if a Contractor is buying less of a good in current month than last month

Reactivations - if a Contractor is buying a good that hadn't had last month (but is not a first time buying it - IsfirstOfThisGoodForContractor = FALSE)

Churn - if a Contractor had a good last month, but not buying it current month.

Please find an example below:

My calculations are:

LastMonth = CALCULATE(SUM('Invoice Contents'[Netto]), PARALLELPERIOD(Dates[Date], -1,MONTH))
 
CurrentMonth = SUM('Invoice Contents'[Netto])
 
Existing = [LastMonth]
 
New = CALCULATE( SUM('Invoice Contents'[Netto]) , FILTER('Invoice Contents', 'Invoice Contents'[IsfirstOfThisGoodForContractor] = TRUE()) )

Upgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] < [CurrentMonth], [CurrentMonth] - [LastMonth] ) ) )
 
Downgrades = IF ([LastMonth] > 0 && [CurrentMonth] > 0 ,  ( IF ( [LastMonth] > [CurrentMonth], [CurrentMonth] - [LastMonth] )  ) )
 
Reactivations = IF ([LastMonth] = 0 && [CurrentMonth] > 0, CALCULATE( SUM('Invoice Contents'[Netto]) , FILTER('Invoice Contents', 'Invoice Contents'[IsFirstOfThisGoodForContractor] = FALSE())))
 
Churn = IF ([LastMonth] > 0 && [CurrentMonth] = 0 , [CurrentMonth] - [LastMonth] )

My desired outcome would be like this:

Thanks in advance for any hints.

Regards,

Krzysztof

[Ashish_Mathur · Accepted solution]

Hi,

Try this measure

Measure = SUMX(SUMMARIZE(Goods,Goods[GoodId],"ABCD",[Downgrades]),[ABCD])

See the last column of the table below

[kuczkrz]

Thank you Ashish, it worked perefectly in this scenario!

[Ashish_Mathur]

You are welcome.