Error showing for date column

[Anonymous]

Hi ,

I am trying to create a triangle in PowerBI. Below is a sample table

Month	Year	EOMONTH
January	2016	1/31/2016
February	2016	2/29/2016
March	2016	3/31/2016
April	2016	4/30/2016
May	2016	5/31/2016
June	2016	6/30/2016
July	2016	7/31/2016
August	2016	8/31/2016
September	2016	9/30/2016
October	2016	10/31/2016
November	2016	11/30/2016
December	2016	12/31/2016

0001 is a created measure and the formula for the measure 002 is IF(DATEADD(Query4[EOMONTH],1,MONTH)> DATE(2019,04,30),0,Query4[0001]-Query4[CC]). But it shows an error says "couldn't load the visual ,a table of multiple columns were supplied were a single value ws expected". I think the EOMONTH column is not taking the date for February. If I select only one month, it gives the measure 002.

Struggling with this for a couple of days. Any help would be greatly appreciated.

Thanks,

[Cmcmahan]

Let's start with the measure:

IF(DATEADD(Query4[EOMONTH],1,MONTH)> DATE(2019,04,30),0,Query4[0001]-Query4[CC])

So you add 1 month to the EOMONTH, and if that date is after April 30, 2019, return 0.  Otherwise return the measure [0001] - the column (or measure?) [CC].  Somehow, Query4[0001]-Querty4[CC] is returning multiple columns of data, unless you filter the context down to a single month.

When you put the data into a table format, it automatically filters it down to the individual month, so you get a single value.  What context are you otherwise trying to evaluate 002 in?  

It would greatly help if you could share the code for measure 001, and CC so we can see what they're returning in various contexts. 

[Anonymous]

Thank you for the response

AA = calculate(Count(Query4[CombinedCount]))

BB = CALCULATE(COUNT(Query4[CombinedCount]),Query4[Cancelno]="1")

CC = CALCULATE(COUNT(Query4[CombinedCount]),Query4[Cancelno]="2")

D = CALCULATE(COUNT(Query4[CombinedCount]),Query4[Cancelno]="3")

 0001 = Query4[AA]-Query4[BB]

002=IF(DATEADD(Query4[EOMONTH],1,MONTH)> DATE(2019,04,30),0,Query4[0001]-Query4[CC])

003 = IF(DATEADD(Query4[EOMONTH],2,MONTH) > DATE(2019,4,30),0,Query4[0002]-Query4[D])

This is how my formula going. 

[Cmcmahan · Accepted solution]

Thanks!

So looking at this, where are you trying to calculate 002?  If it's in a table like the one in your first post, it should work every time.  If you're trying to create a card or other visual that shows 002 without specifying a row, it would give an error unless you filter down to a single month.  

Right now, your 002 IF statement reads like "If every date in the date column plus one month is after Apr 30, then do X, else Y"
If you change it to this, it will select the EOmonth from the current context instead of trying to use the entire column:

002=IF(DATEADD(SELECTEDVALUE(Query4[EOMONTH]),1,MONTH)> DATE(2019,04,30),0,Query4[0001]-Query4[CC])