DAX formula issue? Sales this week number (calculated column) not equal to actual sales number

[Miave]

Seeking some help please. the calulated column (MRR created this week) has totally different value vs actual MRR (green column). Please advise if there is something wrong with DAX? Much appreciated any great help and advice. Thank you.

[v-nuoc-msft]

Hi @Miave 

I understand that your question is Sales this week number (calculated column) not equal to actual sales number.

Here's a table I created based on your screenshots

Here are the modifications I've made to your dax:

MRR Created This week = CALCULATE(SUM(APAC_MidMarket_fromSFDC[Opportunity Gross MRR(converted)]),
 FILTER(ALL(APAC_MidMarket_fromSFDC), [WeekNumber] = MAX([WeekNumber])))

Here is the result

If you're still having problems, provide some dummy data and the desired outcome. It is best presented in the form of a table.

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miave]

Hi @v-nuoc-msft , 

Thanks for looking into my issue. I follow your modified DAX unfortunately i didn't get the same outcome. Meanwhile, I notice in your result screenshot, the "weeknumber" are not in sequence and the value on "Created last week" seems get impacted as well. Please advise how to fix. Thank you very much.

[v-nuoc-msft]

Hi @Miave 

 

For your question I looked into it again, if you want “MRR Created This week” to be equal to “Opportunity Gross MRR(converted)”, you can try the way:

 

If you want to choose to create a measure:

MRR Created This week = SUM('APAC_MidMarket_fromSFDC'[Opportunity Gross MRR(converted)])

If you want to choose to create a calculated column:

MRR Created This week = [Opportunity Gross MRR(converted)]

Regarding your question about "WeekNumber" not being sorted in order and "MRR Created Last Week" being affected, since the "MRR Created This week" column is sorted from highest to lowest, the order of the two columns is changed.

The column can be reordered by clicking on "WeekNumber".

Again, I have a question for you:

I don't know what the "WeekRank" you use does with the results you want.

And if there is a relationship between your tables that has the potential to invalidate the dax. This could be the reason why you are not getting the results you want

Best Regards,

Nono Chen

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

[Miave]

Hi @v-nuoc-msft ,

First of all, my sincere apology for late response as I was out of office during holiday season. I just return to work today 🙂

Thank you so much for your feedback and advice.

Actually, i followed some tutorial videos to create DAX in order to look at how much MRR value created this week/ last week.  And by looking at these 2 figures, we can compare the growth% (+ve or -ve), i.e. week over week growth.

It explains why i have to create "week rank" calculated column to "rank" the week numbers based on the "Created date".

You suggested this measure which gives this week MRR however it doesn't give last week's. 

MRR Created This week = SUM('APAC_MidMarket_fromSFDC'[Opportunity Gross MRR(converted)])

As you see the red line i highlight in this screenshot, the "MRR this week" is the same as "MRR last week" in the subsequent week.  With that we are able to calcucate MRR WoW (week over week) change.

I can share you the tutorial video i learned from social media if required. The instructor also validated the steps and the value are match. I followed exactly the steps and DAX however i couldn't get the same result.  Hope it provides you more information and context of what i am trying to achieve. Please advise. Thank you