cancel
Showing results for
Did you mean:
Highlighted
Helper II

## Store count of today and yesterday in Power Bi

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

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.

Many Thanks

Jibran Ishtiaq

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft

## Re: Store count of today and yesterday in Power Bi

@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 =
RETURN
CALCULATE (
DISTINCTCOUNT ( Table1[RegistrationNumber] ),
FILTER ( Table1, Table1[DateAdded] = YesterdayDate )
)```

Best Regards,

Herbert

4 REPLIES 4
Highlighted
Super User IX

## Re: Store count of today and yesterday in Power Bi

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.

---------------------------------------

Putting square pegs in round holes since 1972.

##### I have a NEW book! DAX Cookbook from Packt
Over 120 DAX Recipes!

Proud to be a Super User!

Highlighted
Microsoft

## Re: Store count of today and yesterday in Power Bi

@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 =
RETURN
CALCULATE (
DISTINCTCOUNT ( Table1[RegistrationNumber] ),
FILTER ( Table1, Table1[DateAdded] = YesterdayDate )
)```

Best Regards,

Herbert

Highlighted
Helper II

## Re: Store count of today and yesterday in Power Bi

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

Highlighted
Helper II

## Re: Store count of today and yesterday in Power Bi

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

Announcements

#### Meet the 2020 Season 2 Power BI Super Users!

Find out who's part of the program this season, and welcome the new Super Users.

#### August 2020 Community Challenge: Can You Solve These?

We're excited to announce our first cross-community 'Can You Solve These?' challenge!

#### July 2020 Community Highlights

Learn about the exciting things that happened in July.

#### Featured Data Story of The Month

All Data Stories Gallery contributions are reviewed for each month. We select a contribution and feature the community member the following month.

#### Power BI Dev Camp - Developing with .NET Core

Learn how to develop custom web applications for Power BI using .NET Core 3.1 and .NET 5.

Top Solution Authors
Top Kudoed Authors