cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Frequent Visitor

Count totals for current date and previous date

Report Date   Cat
17/03/20Correct
17/03/20Wrong
17/03/20Deduct
17/03/20Missing
06/03/20Deduct
06/03/20

Correct

06/03/20Wrong

25/09/19

Correct
15/09/19Missing

 

Good eveneing/afternoon/morning all,

 

Based on the above table, i would like to create two measures to count the 'Cat' for the current (17/03/20) & previous (06/03/20) 'Report Date' where 'Cat' does note equal "Correct".


The data within the table above will be updated frequently and new 'Report Dates' will be added. The measures will need
to dynamically understand what is the date of 'Current Report Date' & 'Last Report Date'.

Based on the above table the two measures will provide the following result:-
Current report = 3
Last Report = 2

Ultimately i want to deduct the difference of the 'Current Report' and 'last report' and add that value to a card.

 

Any help will be overwhelmingly appreciated

1 ACCEPTED SOLUTION

Accepted Solutions
Highlighted
Community Champion
Community Champion

Re: Count totals for current date and previous date

These expressions will generate the values you are looking for in your card visuals.

 

Current Report Date = var __currentdate = MAX('Table'[Report Date])
return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__currentdate)

 

Last Report Date = var __currentdate = MAX('Table'[Report Date])
var __lastdate = CALCULATE(MAX('Table'[Report Date]), 'Table'[Report Date]<__currentdate)
return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__lastdate)
 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

8 REPLIES 8
Highlighted
Community Champion
Community Champion

Re: Count totals for current date and previous date

These expressions will generate the values you are looking for in your card visuals.

 

Current Report Date = var __currentdate = MAX('Table'[Report Date])
return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__currentdate)

 

Last Report Date = var __currentdate = MAX('Table'[Report Date])
var __lastdate = CALCULATE(MAX('Table'[Report Date]), 'Table'[Report Date]<__currentdate)
return CALCULATE(COUNTROWS('Table'), 'Table'[Cat]<>"Correct", 'Table'[Report Date]=__lastdate)
 

If this works for you, please mark it as solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

View solution in original post

Highlighted
Super User IV
Super User IV

Re: Count totals for current date and previous date

@ClemFandango As a best practice, add date dimension in your model and use it for and time intelligence calculations. Once the date dimension is added, mark it as a date table on table tools.

https://perytus.com/2020/05/22/create-a-basic-date-table-in-your-data-model-for-time-intelligence-ca...

 

Once you have a date dimension in your model, you can take advantage of all time intelligent functon and all these period based calculations will be super easy.

 

Previous Day = CALCULATE ( COUNTROWS ( Table ), DATEADD ( DateTable[Date], -1, DAY ), <<other filter condition>> )


Previous Day = CALCULATE ( COUNTROWS ( Table ), PREVIOUSDAY ( DateTable[Date]), <<other filter condition>> )


 

Let's follow the best practice for a more scalable solution and take advantage of Time Intelligence function. Why not to make calculations complicated?

 

I would  Kudos if my solution helped. 👉 If you can spend time posting the question, you can also make efforts to give Kudos whoever helped to solve your problem. It is a token of appreciation!






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User IV
Super User IV

Re: Count totals for current date and previous date

@ClemFandango , witch help from a date calendar

Last Day Non Continous = CALCULATE(sum('order'[Qty]),filter(all('Date'),'Date'[Date] =MAXX(FILTER(all('Date'),'Date'[Date]<max('Date'[Date])),Table['Date'])))
Day behind Sales = CALCULATE(SUM(Sales[Sales Amount]),dateadd('Date'[Date],-1,Day))


This Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])))
Last Day = CALCULATE(sum('order'[Qty]), FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))

 

To get the best of the time intelligence function. Make sure you have a date calendar and it has been marked as the date in model view. Also, join it with the date column of your fact/s. Refer :
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
https://www.archerpoint.com/blog/Posts/creating-date-table-power-bi
https://www.sqlbi.com/articles/creating-a-simple-date-table-in-dax/

See if my webinar on Time Intelligence can help: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/PowerBI-Time-Intelligence-Calendar-WTD-Y...


Appreciate your Kudos.



Did I answer your question? Mark my post as a solution! Appreciate your Kudos!!
My Recent Blog -Week is not so Weak Connect on Linkedin

Proud to be a Super User!

Highlighted
Frequent Visitor

Re: Count totals for current date and previous date

Hi mahoneypat!

 

Many many thanks for this. I am currently unable to try this for a few days.

I will leave some feedback as soon as i can.

 

Do you know if this method will work with a slicer?

 

As an example, I have another column for 'Item' (This contains one of either, item1, item2, item3, item4, item5, item6). Would i be able to use a slicer to display the result of 'Last Report Date' for each individual item in a Card?

 

Thanks again for all of your help

Highlighted
Frequent Visitor

Re: Count totals for current date and previous date

Hey parry2k & amitchandak,

 

Many thanks for your suggestions. I think adding a date dimension is probably the desired long term solution.

 

I will hopefully get some time to try it out later this week and give you some feedback. (after i have worked out a quick short term solution).

 

Many thanks again for taking the time to help me out

Highlighted
Community Champion
Community Champion

Re: Count totals for current date and previous date

Yes.  Those measures should work with slicers and in a table visual.  Please let me know if not.

 

If this works for you, please mark it as solution.  Kudos are appreciated too.

Regards,

Pat

Highlighted
Frequent Visitor

Re: Count totals for current date and previous date

Thanks mahoneypat, your method worked wonderfully.
 
parry2k & amitchandak, thanks massivley for your contribution. I will eventually try to incorparate a date dimension as you suggested, as it sounds like best practice.
Highlighted
Super User IV
Super User IV

Re: Count totals for current date and previous date

@ClemFandango solving a problem is one thing,  following best practice and having a scalable solution is another thing. Always always when you are working with dates, have a date dimension in your model, even if you are not working with dates, still have one, I haven't seen any report which is not have anything to do with dates. My 2 cents and good luck.






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

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Helpful resources

Announcements
June 2020 Community Highlights

June 2020 Community Highlights

Featured community members, changes to the Community, and more! Read up on recent Power BI community news.

Using the Community

Using the Community

Need help with the Power BI Community? Our 'Using the Community' support articles are a great place to start.

Community Summit North America

Community Summit North America

Innovate, Collaborate, Grow. The top training and networking event across the globe for Microsoft Business Applications

Power Platform 2020 release wave 2 plan

Power Platform 2020 release wave 2 plan

Features releasing from October 2020 through March 2021

Top Solution Authors
Top Kudoed Authors