DAX for monthly data with missing values

[mterry]

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

[v-lili6-msft · Accepted solution]

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

[mterry]

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?

[v-lili6-msft]

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

[mterry]

Thanks for the help