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.
Hello,
I'm trying to create a summarized table in DAX that extracts customer info from my main data table and adds in the missing rows for months based on the last date in a different calculated month column. I'll be very happy if anyone can solve this for me.
Here is a simplified data extract, also showing where the data in the columns has come from. The last column ("alternative") is conditional based on the flag column (shows a different date if the flag = 1) - see last row.
Source : | Orig Data | Orig Data | DAX Column | PQ Column | DAX Column |
Column Name : | Company | ID | Flag | Active Month | Alternative Active Month |
Data : | Company A | 1234 | 0 | Jan-21 | Jan-21 |
Company A | 1234 | 0 | Feb-21 | Feb-21 | |
Company A | 1234 | 0 | Mar-21 | Mar-21 | |
Company A | 1234 | 0 | Apr-21 | Apr-21 | |
Company A | 1234 | 0 | May-21 | May-21 | |
Company A | 1234 | 0 | Jun-21 | Jun-21 | |
Company A | 1234 | 0 | Jul-21 | Jul-21 | |
Company A | 1234 | 1 | Aug-21 | Oct-21 |
I'm trying to insert/create the "missing" rows of September and October ie. fill in the gap between the last "active month" of August and the last "alternative active month" of October. This is so I can create visuals that will recognise the flag as being active in August, September AND October. I've been trying ADDMISSINGITEMS with SUMMARIZEDCOLUMNS but cannot get it to give the answer I need. I'm looking to ideally update just the existing "active month" column, but if instead I need to create a third date column that actually does the job that would be fine as the data set is not enormous :
Company | ID | Flag | Active Month | Alternative Active Month | NEW Month Column |
Company A | 1234 | 0 | Jan-21 | Jan-21 | Jan-21 |
Company A | 1234 | 0 | Feb-21 | Feb-21 | Feb-21 |
Company A | 1234 | 0 | Mar-21 | Mar-21 | Mar-21 |
Company A | 1234 | 0 | Apr-21 | Apr-21 | Apr-21 |
Company A | 1234 | 0 | May-21 | May-21 | May-21 |
Company A | 1234 | 0 | Jun-21 | Jun-21 | Jun-21 |
Company A | 1234 | 0 | Jul-21 | Jul-21 | Jul-21 |
Company A | 1234 | 1 | Aug-21 | Oct-21 | Aug-21 |
Company A | 1234 | 1 | Sep-21 | Oct-21 | Sep-21 |
Company A | 1234 | 1 | Oct-21 | Oct-21 | Oct-21 |
Thank-you
Solved! Go to Solution.
You can't do this in the same table with DAX. You have to define a new one.
Assuming your month columns are date type, you can define a new calculated table like this:
NewTable=
GENERATE (
Orig,
VAR N = DATEDIFF ( Orig[Active Month], Orig[Alternative Active Month], MONTH )
RETURN
SELECTCOLUMNS (
GENERATESERIES ( 0, N ),
"New Month", EOMONTH ( Orig[Active Month], [Value] )
)
)
You can't do this in the same table with DAX. You have to define a new one.
Assuming your month columns are date type, you can define a new calculated table like this:
NewTable=
GENERATE (
Orig,
VAR N = DATEDIFF ( Orig[Active Month], Orig[Alternative Active Month], MONTH )
RETURN
SELECTCOLUMNS (
GENERATESERIES ( 0, N ),
"New Month", EOMONTH ( Orig[Active Month], [Value] )
)
)
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 | |
106 | |
79 | |
71 | |
66 |
User | Count |
---|---|
141 | |
107 | |
100 | |
82 | |
74 |