Forum Discussion

Graitec's avatar
Graitec
Regular Visitor
6 years ago
Solved

Visual filter from a DAX Sumx filter expression

Hi,   I want to query the number of licences which are active for each week so I can show the increase in licences over time.  I have created two date tables as below which work, I suspect there is...
Need Help
  • v-deddai1-msft's avatar
    v-deddai1-msft
    6 years ago

    Hi Graitec ,

     

    Would you please refer to the measure below:

    Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<MAX('Dates - Week/Year'[WeekStartDate])),Purchases[Quantity])

     

    Best Regards,

    Dedmon Dai

Graitec's avatar
Graitec
Regular Visitor
6 years ago

Could you help me create the measure that would give me the same data as the column I currently have.  Basically what I want is a count for each week of the number of licence on that date, so it would have to query the date of each week and use that date in the DAX to come up with with the count of licences.  This is why I thought I couldn't do this in a measure because it need to be done for each row of the table.

 

Below is the current column calculation, I've simplified a bit further just to hopefully explain what I'm trying to achieve, the part in bold is the data from that row of the table, can this be done using a measure?

 

Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<'Dates - Week/Year'[WeekStartDate]),Purchases[Quantity])

 

 

v-deddai1-msft's avatar
v-deddai1-msft
Community Support
6 years ago

Hi Graitec ,

 

Would you please refer to the measure below:

Number of Active Licences = Sumx(filter(Purchases,Purchases[License Expiry Date]<MAX('Dates - Week/Year'[WeekStartDate])),Purchases[Quantity])

 

Best Regards,

Dedmon Dai

  • Graitec's avatar
    Graitec
    Regular Visitor
    6 years ago

    This worked perfectly, thanks very much Dedmon!