Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago
Solved

CrossJoin for blank dates vs defined dates

Hello to everyone

 

I need help since I managed to crossjoin in a new table all values that are defined for specific dates.

However, i've got the standard prices for the non-campaign times (Which have blank dates):

 

FILTER(CROSSJOIN(Table1,'Table2), Table1[Week]>='Table2'[inicio] && Table1[Week]<='Table2'[fim] && Table1[Bundle2]='Table1 2'[PP])
 
However this is matching me only the values which have a promo. is it possible to create a new table only for not matching dates (which in this case are blanks) and arent subject to the period conditions?
 
Thanks
Need Help
  • lbendlin's avatar
    lbendlin
    5 years ago

    Create a CALENDAR() table that covers the entire range of dates you want to report against, then use GENERATESERIES() for the dates where you have events, and finally EXCEPT() the two tables which will give you all the dates that do NOT have events.

4 Replies

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Is 'Table1 2'  a typo?

     

    Are you familiar with the EXCEPT() and INTERSECT() functions?

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Hello,

       

      It's just a typo, yes. should just read Table2.

       

      Yeah. I am familiar with those, however not managing to apply them to this here.

       

      Thanks.

  • lbendlin's avatar
    lbendlin
    Icon for Super User rankSuper User
    5 years ago

    Create a CALENDAR() table that covers the entire range of dates you want to report against, then use GENERATESERIES() for the dates where you have events, and finally EXCEPT() the two tables which will give you all the dates that do NOT have events.

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      Thanks Mate!