Retrieve data only from the first day the store is active

[netanel]

Hi All,

I want to retrieve data only from the first day the store is active

Only from the first purchase, only since then, I want to record 0 where there is no activity.

My main problem is that my database
Does not record days in the DB in which no activity is performed (does not enter them at all in FACT)
That's why I can't register 0 on such days in DAX

I try this Measure to create a flag that tells my the first activity 

HasDataAfterFirst =

VAR MinDateWithTotal = CALCULATE(MIN('Fact_Transactions'[Transaction_DateKey]), ALL('Dim_Date'))

RETURN IF('Dim_Date'[DateKey] >= MinDateWithTotal, 1, 0)

The problem with this measure is that it brings me the first activity of the first store, and I want that if I filter for another store it will start from the beginning of that store's activity.

So I try this measure

Total_Funded_After_MinDate =

CALCULATE(

    SUM('Fact_Transactions'[Total_Funded_USD]),

    FILTER(

        'Fact_Transactions',

        'Fact_Transactions'[Transaction_DateKey] >= SELECTEDVALUE(FilterZero[first_date])

    )

)

In this approach, I created another table with three columns
Start date of each store
The ID of each store
and source

But this is the result that I get

In the picture, you can see that on the days there is no recorded activity the graph skips on them

If I use the Show item with no data option

I get all the dates for the start of my dim date

Sorry I can't share the PBIX file

Would appreciate help

[amitchandak]

@netanel , You need to have a measure like

HasDataAfterFirst =
VAR MinDateWithTotal = CALCULATE(MIN('Fact_Transactions'[Transaction_DateKey]), filter(all('Fact_Transactions'), 'Fact_Transactions'[Store] = earlier('Fact_Transactions'[Store])))
RETURN IF(max('Dim_Date'[DateKey]) >= MinDateWithTotal, 1, 0)

[netanel]

Hi @amitchandak 

Thanks for the quick response 

first, it can't be a measure because of the earlier row context 

EARLIER/EARLIEST refers to an earlier row context that doesn't exist.

So I create a new column in my Fact_transactions

look like that

HasDataAfterFirst 2 =

VAR MinDateWithTotal = CALCULATE(MIN('Fact_Transactions'[Transaction_DateKey]), filter(all('Fact_Transactions'), 'Fact_Transactions'[Merchant_TransactionKey] = earlier('Fact_Transactions'[Merchant_TransactionKey])))

RETURN IF(max('Dim_Date'[DateKey]) >= MinDateWithTotal, 1, 0)

This option is not working, it should be remembered that creating this flag actually serves as a filter later on in the visualization for showing store number 1. I want to receive all the dates.