November 14, 2018 at 11:32 pm
I have below data set in my SSAS tabular model, for time being i have posted data for one month but the actual dataset contains N number of months.
Requirement - If any day data missing in a particular month we have to apply the below logic. in the below case data was missed for 29-Mar-18 ,
Get the before all last four weeks total
(17,936.92 + 21,222.56 + 16,338.08 + 14,054.56 ) / 4 = 17,388.0
and get the prevoius day value of the missing day = 38,057.32
and calculate the percentage (38,057.32 / 17,388.03) * 100 = 218 percent
Now i have to create below report which is showing the total sales amount for march, In case if any month has missing date then we have to apply the above logic and calculate the percent and If the Percentage is greater than 210 % then only we should show the total sales value for than particular month otherwise we have to show blank. In case if data consists for all days in a month then it should be straight forward sum otherwise if any day misses we have to apply the above logic and decide to show are not to show value in the report.
| Seial No | CalenderDate | Amount | DayName |
| 1 | 28-Feb-18 | 17,936.92 | Wed |
| 2 | 1-Mar-18 | $15,762.60 | Thu |
| 3 | 2-Mar-18 | $16,575.20 | Fri |
| 4 | 3-Mar-18 | $11,510.84 | Sat |
| 5 | 4-Mar-18 | $7,402.72 | Sun |
| 6 | 5-Mar-18 | $17,666.52 | Mon |
| 7 | 6-Mar-18 | $17,119.92 | Tue |
| 8 | 7-Mar-18 | 21,222.56 | Wed |
| 9 | 8-Mar-18 | $17,005.28 | Thu |
| 10 | 9-Mar-18 | $19,470.16 | Fri |
| 11 | 10-Mar-18 | $12,438.08 | Sat |
| 12 | 11-Mar-18 | $9,739.52 | Sun |
| 13 | 12-Mar-18 | $15,171.64 | Mon |
| 14 | 13-Mar-18 | $13,884.84 | Tue |
| 15 | 14-Mar-18 | 16,338.08 | Wed |
| 16 | 15-Mar-18 | $14,875.76 | Thu |
| 17 | 16-Mar-18 | $16,716.36 | Fri |
| 18 | 17-Mar-18 | $13,023.04 | Sat |
| 19 | 18-Mar-18 | $9,731.24 | Sun |
| 20 | 19-Mar-18 | $15,625.92 | Mon |
| 21 | 20-Mar-18 | $14,654.92 | Tue |
| 22 | 21-Mar-18 | 14,054.56 | Wed |
| 23 | 22-Mar-18 | $16,035.64 | Thu |
| 24 | 23-Mar-18 | $20,539.36 | Fri |
| 25 | 24-Mar-18 | $14,389.20 | Sat |
| 26 | 25-Mar-18 | $11,889.92 | Sun |
| 27 | 26-Mar-18 | $16,035.76 | Mon |
| 28 | 27-Mar-18 | $20,404.36 | Tue |
| 29 | 28-Mar-18 | 38,057.32 | Wed |
| 30 | 29-Mar-18 | Thu | |
| 31 | 30-Mar-18 | $21,573.36 | Fri |
November 14, 2018 at 11:49 pm
ramrajan - Wednesday, November 14, 2018 11:32 PMI have below data set in my SSAS tabular model, for time being i have posted data for one month but the actual dataset contains N number of months.Requirement - If any day data missing in a particular month we have to apply the below logic. in the below case data was missed for 29-Mar-18 ,
Get the before all last four weeks total
(17,936.92 + 21,222.56 + 16,338.08 + 14,054.56 ) / 4 = 17,388.0
and get the prevoius day value of the missing day = 38,057.32
and calculate the percentage (38,057.32 / 17,388.03) * 100 = 218 percentNow i have to create below report which is showing the total sales amount for march, In case if any month has missing date then we have to apply the above logic and calculate the percent and If the Percentage is greater than 210 % then only we should show the total sales value for than particular month otherwise we have to show blank. In case if data consists for all days in a month then it should be straight forward sum otherwise if any day misses we have to apply the above logic and decide to show are not to show value in the report.
This is not complex and can easily be solved with some measures and a bit of DAX. The problem is, to do so, one has to build a mock model, populate it and then apply the solution, too much work for such a simple problem.
😎
Suggest you do either a PowerBI Desktop or Excel PowerPivot models with realistic sample data to help others helping you.
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply