count of date

[jimpatel]

Hi,

Thanks for looking at my post.

I have measure with some formula to calculate and it is showing in date format only (which is what i need it). Is there any way while showing in table i can show as count total date and show as total please?

Thanks a lot

[jimpatel]

Any idea please

Thanks a lot

[123abc]

It seems like you're looking to convert a date into a count, possibly based on a certain formula, and display the total count in a table in Power BI using Data Analysis Expressions (DAX). Assuming you have a date column and a formula column, here's a general guide on how you might achieve this:

1. Create a New Calculated Column:

   • In Power BI Desktop, go to the "Model" view.
   • Select your table, and then click on "New Column" in the Modeling tab.
   • Use a DAX formula to create a new calculated column. For example:

TotalCount = COUNTAX(FILTER(YourTable, YourFormula), YourTable[DateColumn])

1. Replace YourTable, YourFormula, and DateColumn with your actual table name, formula, and date column.

2. Use the New Column in a Table Visualization:

   • Go back to the "Data" or "Report" view.
   • Create a table visualization.
   • Drag and drop the newly created "TotalCount" column into the table.

Now, your table should display the total count based on your formula for each date.

Remember, the formula provided above is just a basic example. Depending on the nature of your formula and requirements, you may need to adjust it accordingly.

If your formula is more complex, consider sharing additional details about your data structure and the specific calculation you're trying to achieve for a more tailored response.

[jimpatel]

Much appreciated your reply.

I have followed same logic as above but still total is showing 1 instead of 2 in some instance

thanks a lot 

[123abc]

It looks like you're encountering an issue where the total count is showing as 1 instead of the expected 2 in some instances. This could be due to a variety of reasons, and we'll need to troubleshoot it.

Here are a few things you can check:

1. Check Data:

   • Verify that your data contains multiple rows with the same date that you're trying to count. If there is only one row for a specific date, the count will be 1.

2. Formula Adjustment:

   • Ensure that your DAX formula is correctly referencing the date column. Double-check the column names and the table names in your DAX formula.

3. Context Transition Issue:

   • In some cases, the issue may be related to context transition. Try using the following modified formula:

DateCount = CALCULATE(COUNTROWS(FILTER(ALL('YourTable'[YourDateColumn]), 'YourTable'[YourDateColumn] = EARLIER('YourTable'[YourDateColumn]))))

1. • This formula uses the ALL function to remove any existing filters on the date column before applying the count.

2. Aggregation in Visual:

   • Make sure that you are using the correct aggregation method in the table visual for the DateCount column. It should be set to "Don't Summarize" or "Don't Aggregate" to display individual counts for each row.

If the issue persists, providing more details about your dataset structure, the DAX formula you're using, and any additional filters or slicers in your report could help in providing more targeted assistance. Feel free to share more information, and I'll do my best to assist you further.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly and check the tahms_up.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.

[jimpatel]

Much appreciated for your time and help

Glad to know everyone helpful in this forum. I have followed your above steps and no luck. This is the formula i am using and for some reason it is not adding up the total and all column and row total is showing just as 1 instead of addition. May i know where i am going wrong please?

Measure 5 = if(Var a = CALCULATE(MAX('Table (2)'[Date]),ALLEXCEPT('Table (2)','Table (2)'[Order number],'Table (2)'[Operation Description])) return MAXX( FILTER('Table (2)','Table 2)'[Date]=a),'Table 2)'[Date]),1,0)

Much appreciated again

[123abc · Accepted solution]

It appears there's a small typo in your formula, specifically in the table name. It seems like you have an extra parenthesis in the table name, and there's a missing space. Let's correct the formula:

Measure 5 =
IF (
VAR a = CALCULATE (
MAX ( 'Table (2)'[Date] ),
ALLEXCEPT (
'Table (2)',
'Table (2)'[Order number],
'Table (2)'[Operation Description]
)
)
RETURN
MAXX (
FILTER ( 'Table (2)', 'Table (2)'[Date] = a ),
'Table (2)'[Date]
),
1,
0
)

Make sure you correct the typo in both occurrences of the table name to be 'Table (2)' with the correct number of parentheses.

Additionally, please note that the measure you provided seems to be returning 1 or 0 based on a condition, and it's not directly counting the distinct dates. If you want to count the distinct dates, we may need to adjust the formula accordingly. If counting distinct dates is your goal, please provide more information about the logic you want to apply in counting those dates so that I can assist you better.

If this post helps, then please consider Accepting it as the solution to help the other members find it more quickly.

In case there is still a problem, please feel free and explain your issue in detail, It will be my pleasure to assist you in any way I can.