Earn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Can you share some data and the function you use?
Hello,
Thanks for your responses. Clearly, i discovered that the problem is not the measure but the calendar, (weeks). You will notice that the total is the same from the two tables but week 44 from my calender table give a different result.
Please, how to i solve this problem.
Below is a snip shot of my screen.
Thanks,
Hi
It’s very difficult to tell what’s happening in your data set with this less detail.
It’s looks like your week no column in the right-side table is a text and left side table it’s numeric.
How did you relate your transaction detail table with the calendar table?
Can you add the year column to the table in the right side?
Hello,
Yes you're right the week that calculates correctly is date, that is text.
I'm just comfused. Waiting to get to work tomorrow to add more data and see the result.
Will get back to you after i've added more data.
Thanks so much.
Hi,
What i'm trying to calculate is previous week's sales.
Any help on solving it.
Thanks
I suggest you make all week number columns into numeric type. Or you can generate a calculated column with WEEKNUM() functinon.
Then you can create a measure like:
= CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALL ( 'DimDate' ), DimDate[Year] = MAX ( DimDate[Year] ) && DimDate[WeekNumber] = MAX ( DimDate[WeekNumber] - 1 ) ) )
Regards,
Hi Simon,
Thanks so much for your response.
I tried the formula and got an error message; "The MAX function only accepts a column reference as an argument".
what can i do next.
Thanks,
Sorry for my mistake, it should be like:
= CALCULATE ( SUM ( Table[Sales] ), FILTER ( ALL ( 'DimDate' ), DimDate[Year] = MAX ( DimDate[Year] ) && DimDate[WeekNumber] = MAX ( DimDate[WeekNumber] ) -1 ) )
Week no is there in your calendar table and transaction table?
This difference can occur due to many reasons. Since you have two week no columns I can think of below causes,
1. Since you're using two different week no columns results may differ, try to use one column coz that will confuse you.
2. Since week no column is a text in one scenario and numeric in other scenario summation may result in different values due to duplicates
User | Count |
---|---|
103 | |
88 | |
78 | |
71 | |
69 |
User | Count |
---|---|
113 | |
99 | |
97 | |
72 | |
68 |