Add a column calculating frequency of a date in the previous column (not a measure)

[collis]

I've been using PowerBI for a while, but I am so stuck. It seems simple.

I want the fourth column to calculate the frequency of the dates in the "DatesActive" column. Each row represents an activity on a day. Knowing how many activities happen on a given day (new frequency column) will allow me to produce average and max across time measures.

The first row should return 1 in the "Column" column as only one recorded activity exists. The 2nd row should show 3 as there are three rows with the same date.  

Any assistance would be greatly appreciated.

[Anonymous]

Hi  @collis ,

You may try the following dax:

Measure =
AVERAGEX(
    FILTER(ALL('Table'),
    'Table'[Unpivot.Region]=MAX('Table'[Unpivot.Region])&&
    'Table'[DateActive]=MAX('Table'[DateActive])),[FreqPerDay])

Best Regards,

Liu Yang

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

[collis]

Thank you all. That worked!.

And, just like that... I hit another issue. The solution allows me to creative an average and MAX which is great. However when I filter by location (not in the screenshot), but there are activities in different cities, the frequency, Max and Average is no longer accurate. I think I might need a measures for each location after all. 

e.g., SydneyAverageFrequency = where the "Location" column equals "Sydney", determine the average frequency of activities per day (as per Table 1 above)  

Can anyone help with figuring out that measure code in DAX... that is way beyond my level of ability.

My head is hurting!

[Ritaf1983]

Hi @collis  again 🙂
Please provide a workable sample data and your expected result from that. It is hard to figure out what you want to achieve from the description alone.  

https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-...

[collis]

You're absolutely right. Very confusing

Thanks to this forum, the frequency is working now. 😍 I have calculated average and max for the dataset.

I also need to display the average daily activity count in different locations. As this can't be done dynamically in a table (I assume), I am guessing that would need to be a measure. Can someone help define the measure? 

[Ritaf1983]

Hi @collis 
There is a lot of methods to achieve a wanted result.
One of them :

counter =

CALCULATE(DISTINCTCOUNT('Table'[Index]),ALLEXCEPT('Table','Table'[DatesActive]))

Pbix is attahced

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

[shafiz_p]

Hi @collis  Try below code:

Frequency = 
VAR CurrentDate = 'YourTable'[DatesActive]
RETURN
CALCULATE(
    COUNTROWS('YourTable'),
    FILTER(
        'YourTable',
        'YourTable'[DatesActive] = CurrentDate
    )
)

Hope this helps!!

If this solved your problem, please accept it as a solution and a kudos!!

Best Regards,

Shahariar Hafiz

[VahidDM]

Hi @collis 

Check this DAX query:

Frequency = 
COUNTROWS(
    FILTER(
        YourTableName,
        YourTableName[DatesActive] = EARLIER(YourTableName[DatesActive])
    )
)

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

Appreciate your Kudos!! 

LinkedIn|Twitter|Blog |YouTube 

[Irwan]

hello @collis 

please check if this accomodate your need.

create new calculated column with following DAX.

Column =
COUNTX(
    FILTER(
        'Table',
        'Table'[DatesActive]=EARLIER('Table'[DatesActive])
    ),
    'Table'[Count]
)

Hope this will help.

Thank you.