cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper II
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 

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

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Microsoft
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 =
    DATEADD ( Table1[DateAdded], -1, DAY )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[RegistrationNumber] ),
        FILTER ( Table1, Table1[DateAdded] = YesterdayDate )
    )

Store count of today and yesterday in Power Bi_1.jpg

 

Best Regards,

Herbert

View solution in original post

4 REPLIES 4
Highlighted
Super User IX
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!




Did I answer your question? Mark my post as a solution!

Proud to be a Super User!




Highlighted
Microsoft
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 =
    DATEADD ( Table1[DateAdded], -1, DAY )
RETURN
    CALCULATE (
        DISTINCTCOUNT ( Table1[RegistrationNumber] ),
        FILTER ( Table1, Table1[DateAdded] = YesterdayDate )
    )

Store count of today and yesterday in Power Bi_1.jpg

 

Best Regards,

Herbert

View solution in original post

Highlighted
Helper II
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
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

Today Count.JPG

Many Thanks 

 

Jibran Ishtiaq

 

 

 

Helpful resources

Announcements
Meet the 2020 Season 2 Power BI Super Users!

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?

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

July 2020 Community Highlights

Learn about the exciting things that happened in July.

Featured Data Story of The Month

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

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