Forum Discussion

Anonymous's avatar
Anonymous
Not applicable
5 years ago

Measure Using A Data Log Table

Hello, I have a sales log table described below, and a general calendar table with dates used for analysis. I'm looking to create a measure on the calendar table that will count the SaleStatus for each SaleID. For example on 10/15/2020, it will grab ID=1,4, and 6 because they were the most recent before that date. So 2 Open and 1 Closed.

 

IDSaleIDSaleStatusDate
122Open10/1/2020
222Closed11/1/2020
313Open9/1/2020
413Closed10/1/2020
513Closed11/1/2020
64Open10/1/2020
74Open11/1/2020

3 Replies

  • rfigtree's avatar
    rfigtree
    Resolver III
    5 years ago

    If is this what you mean.

    Seems to Work but probably a better way.

    Try doing this with the wife yelling at me to come get dinner and dog haslting me for a walk.

    mStatus:=var _salesid = CALCULATE(if(HASONEVALUE(Table1[SaleID]),VALUES(Table1[SaleID]),2),ALL('Calendar'))
    var _maxDate=MAX('Calendar'[Date])
    var _x = CALCULATE(MAX(Table1[ID]),FILTER(ALL(Table1),[SaleID]=_salesid),FILTER(ALL('Calendar'),'Calendar'[Date]<=_maxDate))
    return CALCULATE(IF(HASONEVALUE(Table1[ID]),VALUES(Table1[SaleStatus])),FILTER(ALL(Table1),Table1[ID]=_x))

     

     

    • Anonymous's avatar
      Anonymous
      Not applicable
      5 years ago

      This seems to work when using SalesID as a row constraint, but still looking to use this in a PowerBI that just aggregates the count of Open/Closed on each date without displaying each SaleID. I'm also curious, in your variable _salesid, why would the IF statement return 2 as the third argument? 

  • AnkitKukreja's avatar
    AnkitKukreja
    Super User
    5 years ago

    Hi

    Please try this:


    Count =
    VAR _MaxDate = CALCULATE( MAX( Calender[Date] ) , ALLSELECTED( Calender) )

    VAR _MinDate = CALCULATE( MIN( Calender[Date] ) , All(Calender[Date] ) )

    VAR _Count = IF( SELECTEDVALUE( Calender[Date] ) <= _MaxDate ,
    CALCULATE( COUNT( 'Sales Log'[ID] ) , FILTER( ALLSELECTED( Calender[Date]),
    Calender[Date] <= _MaxDate && Calender[Date] >= _MinDate )), BLANK() )
    Return
    _Count


    Thanks,
    Ankit