Forum Discussion
DAX for monthly data with missing values
I have output data for several locations spanning multiple years (a portion of the data is attached). Some locations have consistent output throughout the year, some will have a change at one point during the year. If there isn't a change, then there is no data recorded in the 'date' column - the only date data to go off of would be the year. What I'm trying to do is show a monthly output calculation rather than annual. It would sum the total output each month and incorporate the changes & dates. So for the sample data set, the output for January 2018 would be 169,905, but for March 2018 it would be 155,906 to reflect the changes that took place in Tag A and Tag N in Feb.
| Tag | Year | Beginning Output | Date | Ending Output |
| A | 2018 | 14915 | 2/22/2018 | 6806 |
| B | 2018 | 5047 | 11/1/2018 | 3641 |
| C | 2018 | 35375 | 35375 | |
| D | 2018 | 2707 | 2707 | |
| E | 2018 | 8256 | 8256 | |
| F | 2018 | 9400 | 9400 | |
| G | 2018 | 4468 | 4468 | |
| H | 2018 | 4240 | 4240 | |
| I | 2018 | 229 | 10/31/2018 | |
| J | 2018 | 12022 | 12022 | |
| K | 2018 | 6825 | 6825 | |
| L | 2018 | 7533 | 7533 | |
| M | 2018 | 8338 | 8338 | |
| N | 2018 | 5890 | 2/28/2018 | |
| O | 2018 | 942 | 942 | |
| P | 2018 | 2191 | 2191 | |
| Q | 2018 | 3608 | 3608 | |
| R | 2018 | 10720 | 10720 | |
| S | 2018 | 6251 | 6251 | |
| T | 2018 | 15600 | 15600 | |
| U | 2018 | 1/1/2018 | 5348 | |
| A | 2019 | 6806 | 6806 | |
| B | 2019 | 3641 | 5/1/2019 | 5047 |
| C | 2019 | 35375 | 1/14/2019 | 38541 |
| D | 2019 | 2707 | 2707 | |
| E | 2019 | 8256 | 8256 | |
| F | 2019 | 9400 | 9400 | |
| G | 2019 | 4468 | 4468 | |
| H | 2019 | 4240 | 4240 | |
| I | 2019 | 12022 | 12022 | |
| J | 2019 | 6825 | 6825 | |
| K | 2019 | 7533 | 7533 | |
| L | 2019 | 8338 | 8338 | |
| M | 2019 | 942 | 942 | |
| N | 2019 | 2191 | 12/31/2019 | |
| O | 2019 | 3608 | 3608 | |
| P | 2019 | 10720 | 10720 | |
| Q | 2019 | 6251 | 6251 | |
| R | 2019 | 15600 | 15600 | |
| S | 2019 | 5348 | 5348 |
hi, mterry
Based on my research, you could try this way:
Step1:
Create a dim year month date table or you could just use this formula to create a new dim table
Dim Date = SELECTCOLUMNS ( FILTER ( CALENDAR ( "2018-01-01", "2019-12-31" ), DAY ( [Date] ) = 1 ), "Year No", YEAR ( [Date] ), "Month No", MONTH ( [Date] ), "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )Step2:
Add month column for date column in your basic table
Step3:
Use this formula to create a new table
Table = FILTER(GENERATE(Basic,'Dim Date'),Basic[Year]='Dim Date'[Year No])
Step4:
In the new table, create a real outout column
Real Output = IF('Table'[Month]<='Table'[Month No],'Table'[Ending Output],'Table'[Beginning Output])Step5:
Drag Year month column and real output column from the new table into a visual
Result:
Here is sample pbix file, please try it.
Best Regards,
Lin
4 Replies
hi, mterry
Based on my research, you could try this way:
Step1:
Create a dim year month date table or you could just use this formula to create a new dim table
Dim Date = SELECTCOLUMNS ( FILTER ( CALENDAR ( "2018-01-01", "2019-12-31" ), DAY ( [Date] ) = 1 ), "Year No", YEAR ( [Date] ), "Month No", MONTH ( [Date] ), "Year Month", YEAR ( [Date] ) * 100 + MONTH ( [Date] ) )Step2:
Add month column for date column in your basic table
Step3:
Use this formula to create a new table
Table = FILTER(GENERATE(Basic,'Dim Date'),Basic[Year]='Dim Date'[Year No])
Step4:
In the new table, create a real outout column
Real Output = IF('Table'[Month]<='Table'[Month No],'Table'[Ending Output],'Table'[Beginning Output])Step5:
Drag Year month column and real output column from the new table into a visual
Result:
Here is sample pbix file, please try it.
Best Regards,
Lin
I should have noted that I do have a date table already in use in this report, my apologies. I'll play around with your solution in that table and see if that solves it - seems like that may make more sense than to create a measure?
hi, mterry
Yes, you'd better create a year-month date dim table as above, that will make the solution easier to understand.
Best Regards,
Lin
