Store count of today and yesterday in Power Bi

[jibran]

Hi,

We are moving our SQL stored procedures into Power BI dax formulas. One of the stored proceudre function was:

Compare the todays count with yesterday count.

Total count can be a positive value or a negative value.

We send email of total count daily to the team.

Now we want to embed this logic into power BI so that team can see total count in power bi.

Current Table structure is 

ID  RegistrationNumber  DateAdded

1     XYZ                            01-06-2016

2    ABC                            01-09-2016

3    GHI                             15-01-2017

My question is that how can I achieve this logic in Power BI in which I can add calcualted columns that 

stores today's count and yesterday's count value.

I tried to add calculated column but of no vein, can someone please help me to achieve this target.

Many Thanks 

Jibran Ishtiaq

[v-haibl-msft · Accepted solution]

@jibran

Please try with following DAX column formula if the result is what you desire.

TodayCount = 
VAR CurrentDate =
    CALCULATE ( MAX ( Table1[DateAdded] ) )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[RegistrationNumber] ),
        FILTER ( Table1, Table1[DateAdded] = CurrentDate )
)

YesterdayCount = 
VAR YesterdayDate =
    DATEADD ( Table1[DateAdded], -1, DAY )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[RegistrationNumber] ),
        FILTER ( Table1, Table1[DateAdded] = YesterdayDate )
    )

Best Regards,

Herbert

[jibran]

Hi Herbert,

Sorry to be naive on this, with your help I managed to have today counter column in my Power Bi table.

But I am struggling to find out that how can I dsiplay this on my Power BI dashboard.

Like I would like to display this as Today Count = 7.

Date                                                                                                                                 Today Count

Many Thanks 

Jibran Ishtiaq

[jibran]

Hi, 

Thanks for your help it has worked but how can I display yesterday count in Power Bi Dashboard.

Do I have to use measure to bring today count of vehicles?

Many Thanks

Jibran Ishtiaq

[Greg_Deckler]

I would create a date table and then simply do a custom column like this in your date table:

MyColumn = CALCULATE(COUNT([column]),RELATEDTABLE(Registrations))

This would give you a count for each date in your date table.