Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
Hi,
I have a table like this:-
But I need to add missing data between month, but only first day from each month which value is the same the last month. Like this:
Is there some way to add the missing data?
Thanks
Solved! Go to Solution.
Hi @Anonymous ,
You need to add a new table , please use the following formula to have a try:
New Table =
ADDCOLUMNS (
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Table'[Date] ), TODAY () ),
"Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)
),
"Value",
VAR _filldate =
MAXX ( FILTER ( 'Table', 'Table'[Date] <= EARLIER ( [Date] ) ), [Date] )
RETURN
LOOKUPVALUE ( 'Table'[Value], 'Table'[Date], _filldate )
)
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Hi,
You may download my PBI file from here.
Hope this helps.
Hi @Anonymous ,
You need to add a new table , please use the following formula to have a try:
New Table =
ADDCOLUMNS (
DISTINCT (
SELECTCOLUMNS (
CALENDAR ( MIN ( 'Table'[Date] ), TODAY () ),
"Date", DATE ( YEAR ( [Date] ), MONTH ( [Date] ), 1 )
)
),
"Value",
VAR _filldate =
MAXX ( FILTER ( 'Table', 'Table'[Date] <= EARLIER ( [Date] ) ), [Date] )
RETURN
LOOKUPVALUE ( 'Table'[Value], 'Table'[Date], _filldate )
)
The final output is shown below:
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
107 | |
98 | |
78 | |
65 | |
53 |
User | Count |
---|---|
144 | |
103 | |
98 | |
85 | |
64 |