Forum Discussion

WillEyedowin's avatar
WillEyedowin
Frequent Visitor
7 years ago
Solved

WEEKNUM Relative Date Filtering

Hello All,   I would like to filter my entire report to include all dates(weeknum's) up until the week before current.   E.g. I want my report to include WEEKNUMS 1 through 45 but not week 46 if ...
Need Help
  • Ashish_Mathur's avatar
    Ashish_Mathur
    7 years ago

    Hi,

     

    Does this measure work?

     

    Measure = if(ISBLANK(SUM(sold[Quantity Sold])),BLANK(),1-DIVIDE(SUMX(RELATEDTABLE('sold'),[Quantity Sold]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Delivered]),0))
WillEyedowin's avatar
WillEyedowin
Frequent Visitor
7 years ago

Hi,

 

I changed the link to private last night as I assumed you would have already downloaded it - public again now :) 

Ashish_Mathur's avatar
Ashish_Mathur
Super User
7 years ago

Hi,

 

In the Stores Table, there is no Date column.  There should be a Date column there which should connect to the dateKey Table.

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Hi,

     

    Does this measure work?

     

    Measure = if(ISBLANK(SUM(sold[Quantity Sold])),BLANK(),1-DIVIDE(SUMX(RELATEDTABLE('sold'),[Quantity Sold]),SUMX(RELATEDTABLE('Dispatched'),[Quantity Delivered]),0))
  • WillEyedowin's avatar
    WillEyedowin
    Frequent Visitor
    7 years ago

    The Stores table is just a list of supermarkets - I don't believe a date column would work as a store does not have a date?

  • WillEyedowin's avatar
    WillEyedowin
    Frequent Visitor
    7 years ago

    Hi,

     

    Yes, that works! So just to make sure I understand the DAX:

     

    This checks if the quantity sold sum is blank - if so then then use blank as the data, otherwise use the correct data and graph it?

  • Ashish_Mathur's avatar
    Ashish_Mathur
    Super User
    7 years ago

    Good to know that.  Yes, that is correct.