CODEWITHSUNDEEP
powerbidax
kittysmile
kittysmile

Reputation: 51

DAX Count in every date in range

Hello :) In my model I have a fact table:

FactKey CreatedDateKey ClosedDateKey
12345 20220101 20220601

And 2 date dimension: Created Date and Close Date

I would like to create a measure which presents count of FactKey in every date in which key is valid, for instance:
Created Date | Count
2022-01-01 | 1
2022-01-02 | 1
2022-01-03 | 1
. | .
. | .
. | .
2022-06-01 | 1

Could you please help me to create valid calculation? Thanks in advance!

Upvotes: 0

Views: 990

Answers (2)

Mik
Mik

Reputation: 2103

VAR CurrentDay = SelectedValue(date[date])
VAR filteredFact =
        FILTER(
               ALL(FACT)
               ,AND(
                    [CreatedDateKey]<=CurrentDay
                    ,[ClosedDateKey]>=CurrentDay
               )
         )

RETURN
    COUNTROWS(filterdFact)

Upvotes: 1

Francisco Guiroy
Francisco Guiroy

Reputation: 17

I believe that a good option it's to create a calculated column and then a a DAX like this ->

 IF(ISBLANK(CreatedDateKey),"",1)

What this functions does it's reads the column and checks for nulls, if there's a date inside it will add a 1 to the new column, in the other case it will leave a null

Once you have that result then you can add a KPI Card and then summarize the amount of 1 that you have.

Let me know if this solution works, otherwise I can give further assistance

Upvotes: 0

Related Questions