Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
7 years ago
Solved

Error showing for date column

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 2...
Need Help
Show and Tell
Tips and Tricks
  • Cmcmahan's avatar
    Cmcmahan
    7 years ago

    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])

     

Anonymous's avatar
Anonymous
Not applicable
7 years ago

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's avatar
Cmcmahan
Icon for Resident Rockstar rankResident Rockstar
7 years ago

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])