Measure Using A Data Log Table

[Anonymous]

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.

ID	SaleID	SaleStatus	Date
1	22	Open	10/1/2020
2	22	Closed	11/1/2020
3	13	Open	9/1/2020
4	13	Closed	10/1/2020
5	13	Closed	11/1/2020
6	4	Open	10/1/2020
7	4	Open	11/1/2020

[AnkitKukreja]

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

[rfigtree]

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]

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?