creating a dynamic table in Power BI

[Anonymous]

Hi Experts

I am trying to creat the following in Power BI for the Y Values (dates) as shown in the image below. The image below is an example that someone has done in excel. The question is how would you do this in power BI

image 1.

i just need the result from the table which in excel is sum D4:015 = -44 (based on material_id using my data set) 

Date Table in Power BI

sample power bi file.

https://www.dropbox.com/s/1jq4ddeuy3nxpzq/apples.pbix?dl=0

[Cmcmahan · Accepted solution]

If the error message is about SUMMARIZECOLUMNS and ADDMISSINGITEMS not being allowed in the current context, that's an issue with SUMMARIZECOLUMNS not playing well with slicers on multiple columns.

I re-wrote the measure to use the old ADDCOLUMNS style pattern that was used before SUMMARIZECOLUMNS was introduced:

MK-Stat = 
VAR ComplaintsByFiscalMo = ADDCOLUMNS(VALUES(PMS_COMPLAINT[FISCAL_MON_START_DT]),"CountComplaints", CALCULATE(COUNTROWS(PMS_COMPLAINT)))
RETURN
SUMX(ComplaintsByFiscalMo, 
    SUMX(ComplaintsByFiscalMo,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]>EARLIER([CountComplaints]), 1,
        IF([FISCAL_MON_START_DT]>EARLIER([FISCAL_MON_START_DT])&&[CountComplaints]<EARLIER([CountComplaints]), -1))
    )
)

I've also re-uploaded the change in the .pbix file on Google Drive. The same link will download the newer version.