Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

Reply
Anonymous
Not applicable

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
mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


View solution in original post

8 REPLIES 8
amitchandak
Super User
Super User

@Anonymous , 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-YTD-LYTD-Week-Over-Week/m-p/1051626#M184


Appreciate your Kudos.

Anonymous
Not applicable

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.

@Anonymous 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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

mahoneypat
Employee
Employee

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


Anonymous
Not applicable

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

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





Did I answer your question? Mark my post as a solution! Kudos are also appreciated!

To learn more about Power BI, follow me on Twitter or subscribe on YouTube.


@mahoneypa HoosierBI on YouTube


@Anonymous 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-calculations/

 

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!



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Anonymous
Not applicable

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

Helpful resources

Announcements
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors