Unlock a world of possibilities! Login now and discover the exclusive benefits awaiting you.
Hello Experts,
I'm working with a set of sales data I've encountered an issue with "Previous Month" measures.
The measure is intended to sum the "Amount" values from the previous month based on the maximum date selected, but it returns zero even though there are relevant data entries.
Below is my Data
SalesDate | Product | Amount |
10/1/2023 | Chair | 50 |
15/1/2023 | Fan | 100 |
24/2/2023 | Chair | 82 |
23/2/2023 | Fan | 72 |
14/3/2023 | Chair | 132 |
13/3/2023 | Fan | 56 |
10/1/2024 | Chair | 50 |
15/1/2024 | Fan | 100 |
24/2/2024 | Chair | 82 |
23/2/2024 | Fan | 72 |
14/3/2024 | Chair | 132 |
13/3/2024 | Fan | 56 |
Measure for Sum of Amount for Selected Month (Working Correctly):
sum({<SalesDate = {">=$(=MonthStart(Max(SalesDate)))<=$(=MonthEnd(Max(SalesDate)))"}>} Amount)
Measure for Sum of Amount for Selected Previous Month (Returning Zero):
sum({<SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"}>} Amount)
I have attached my QVF File as well.
Could anyone help identify why the measure for the previous month might be returning zero and suggest how to fix this issue?
Thank you
your expression should look like this:
sum({<SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"},MonthYear=>} Amount)
Your will have to bipass the field in previous year expression:
or
sum(1{<SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"}>} Amount)
your expression should look like this:
sum({<SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"},MonthYear=>} Amount)
Your will have to bipass the field in previous year expression:
or
sum(1{<SalesDate = {">=$(=MonthStart(AddMonths(Max(SalesDate), -1)))<=$(=MonthEnd(AddMonths(Max(SalesDate), -1)))"}>} Amount)