• Hi All

    Please note that at the Chandoo.org Forums there is Zero Tolerance to Spam

    Post Spam and you Will Be Deleted as a User

    Hui...

  • When starting a new post, to receive a quicker and more targeted answer, Please include a sample file in the initial post.

How to Countif with between two date

Oscarr said:
Hi

StartDate and EndDate is the main point what i want, i can choose that date then show me that total=?

i'm get data from excel, because is other department send to me, so you know use StartDate and EndDate to this Power Query? if yes, pls teach me.

Thanks
Click to expand...

Have you been able to open the file and review the Power Query Table?
 
 
Last edited:
Hiii @Oscarr

Please find the attached solution sheet with using Pivot table ..

Just add Column E and Column F

To refresh Pivot table >>>Right click on Pivot table >>>Click Refresh

Column E Fomula
Cell E2:=COUNTIFS([Date],[@Date],[Name],[@Name])

Column F Fomula
Cell F2:=IFERROR(IF(AND([@Date]>=$J$1,[@Date]<=$J$2),1/[@Count],"")+0,)


Regard
Rahul shewale
 

Attachments

  • distint count with pivot table.xlsm
    240.3 KB · Views: 5
rahulshewale1 said:
Hiii @Oscarr

Please find the attached solution sheet with using Pivot table ..

Just add Column E and Column F

To refresh Pivot table >>>Right click on Pivot table >>>Click Refresh

Column E Fomula
Cell E2:=COUNTIFS([Date],[@Date],[Name],[@Name])

Column F Fomula
Cell F2:=IFERROR(IF(AND([@Date]>=$J$1,[@Date]<=$J$2),1/[@Count],"")+0,)


Regard
Rahul shewale
Click to expand...

Hi bro @rahulshewale1

Thanks your help. this formula is good for small data use, mine data until column 1048575, after i try your formula, is too hang.:(
 
 
Hi ,

It is not so tough ; take it in this order :

1. The necessity of a dates table when you deal with Power Pivot ; create it in your Excel worksheet , and then switch to the Power Pivot window , and create a linked table ; specify it to be a dates table.

2. Create a relationship between the dates in your Excel fact table and the dates in the dates table

3. The importance of measures and how to create them ; if you can try out various measures in the Calculation Area of the Power Pivot window , you will gain in confidence.

4. The power of pivot tables when you use Power Pivot ; multiple data sources , and the ability to use the measures you have created in the Power Pivot window.

It is a much longer process than mastering Excel , because Power Pivot does not deal in worksheet cells ; it deals exclusively with data , in the form of tables. Lots of data , in possibly lots of tables ; instead of thinking in terms of worksheet cells , you need to think in terms of tables , columns , rows.

But once you start , it is not as tough as it seems.

Narayan
 
NARAYANK991 said:
Hi ,

It is not so tough ; take it in this order :

1. The necessity of a dates table when you deal with Power Pivot ; create it in your Excel worksheet , and then switch to the Power Pivot window , and create a linked table ; specify it to be a dates table.

2. Create a relationship between the dates in your Excel fact table and the dates in the dates table

3. The importance of measures and how to create them ; if you can try out various measures in the Calculation Area of the Power Pivot window , you will gain in confidence.

4. The power of pivot tables when you use Power Pivot ; multiple data sources , and the ability to use the measures you have created in the Power Pivot window.

It is a much longer process than mastering Excel , because Power Pivot does not deal in worksheet cells ; it deals exclusively with data , in the form of tables. Lots of data , in possibly lots of tables ; instead of thinking in terms of worksheet cells , you need to think in terms of tables , columns , rows.

But once you start , it is not as tough as it seems.

Narayan
Click to expand...

Hi Bro

Thanks your help...i need to read and try first..
 
Hii @Oscarr

Please find the attached solution using power quary .Please Go through below step

download excel files below link

https://www.dropbox.com/s/du3kx37do02yfv3/distint count with pivot table - Copy.xlsm?dl=0

1.Click anywhere in table.
2.You will see Worksheet Queries
3.Right Click on Table 13 (2)
4.Click on Edit
5.you will see Query setting tab on Left side
6. Click on "Add conditional Column"
7.Go to formula bar (in case formula do not show then go To "View tab" then click "Formula Bar"
8.You have to change Start date and End date According to requirement (screen shot attached )
9 .Click "Remove Column 2" last Step
10. Click on "Close & load "
11. you will get desire output.




Regard
Rahul shewale
 

Attachments

  • Screen shot images.docx
    53.1 KB · Views: 3
rahulshewale1 said:
Hii @Oscarr

Please find the attached solution using power quary .Please Go through below step

download excel files below link

https://www.dropbox.com/s/du3kx37do02yfv3/distint count with pivot table - Copy.xlsm?dl=0

1.Click anywhere in table.
2.You will see Worksheet Queries
3.Right Click on Table 13 (2)
4.Click on Edit
5.you will see Query setting tab on Left side
6. Click on "Add conditional Column"
7.Go to formula bar (in case formula do not show then go To "View tab" then click "Formula Bar"
8.You have to change Start date and End date According to requirement (screen shot attached )
9 .Click "Remove Column 2" last Step
10. Click on "Close & load "
11. you will get desire output.




Regard
Rahul shewale
Click to expand...

Hi Bro @rahulshewale1

this website internal server error has been 2 days ago, can't login and reply anything..==

by the way your this solution is great,fix my problem...thank you very much bro...

but i want ask, if i want to show Product A (Total 10000) and Product B (total 20000), together with this formula between two date can?

pls check that attached pic.

Thanks
 

Attachments

  • Power Query.png
    Power Query.png
    47.5 KB · Views: 7
Last edited:
hii @Oscarr

i did not understand this part Product A (Total 10000) and Product B (total 20000),
Please provide me desire output with small database.


Regard
Rahul shewale
 
rahulshewale1 said:
hii @Oscarr

i did not understand this part Product A (Total 10000) and Product B (total 20000),
Please provide me desire output with small database.


Regard
Rahul shewale
Click to expand...

Hi @rahulshewale1

mean just same with your solution, but just add 2 more column data.

Pls refer the attached file.

Thanks
 

Attachments

  • New distint count with pivot table.xlsx
    10.6 KB · Views: 3
Hii @Oscar ,

Please find the attached solution using power quary .Please Go through below step

download excel files below link
https://www.dropbox.com/s/bqsuqtkpjxwpxp1/Distinctcount with Power Quary(Final).xlsm?dl=0

Step 1
1.Click anywhere in table.
2.You will see Worksheet Queries
3.Right Click on Distinct-count Queries
4.Click on Edit
5.you will see Query setting tab on Left side
6.Click on "start date _End Date" (in Applied steps)
7.Change start date and End date as your requirement
8.Click on "Close & Load "
9.you will get Distinct Count.


For Sum of of Product
1.Right Click on Sumif _table Queries
2.Click on Edit
3.you will see Query setting tab on Left side
4.Click on "start date _End Date" (in Applied steps)
5.Change start date and End date as your requirement
6.Click on "Close & Load "

Get Final result
1.Click on Refresh Button .

Note : it will take 90 second for refresh

Regard
Rahul shewale
upload_2017-9-1_18-11-47.png
 
You must log in or register to reply here.
Back
Top