topic Count the latest week of data available. in DAX Commands and Tips

Count the latest week of data available.

matrix_user — Fri, 15 Jul 2022 23:57:37 GMT

Hello DAXers,

I am in a situation where I cannot get my visual card to show the correct/latest week of data avaiable in my model.

To present my data in a visual card I am currently using the following DAX expression:

Latest week count =

VAR Count = MAX('Date'[WeekNbrYear])RETURN

CALCULATE(COUNT('Fact Table'[Entry_date]),('Date'[WeekNbrYear] = Count))

The "('Fact Table'[Entry_date])" are dates customers have purchased from a store. The data type for this calculated column is set to Date: 14/03/01(d/mm/yy). I want to count each entry date and represent it in a visual card and for it to display only the latest week - be it a full 7 days (Monday to Sunday) or a couple of days (Mon to Tues).

I have no problems displaying card counts for the last day or month available in my model - using the above DAX expression with appropriate modifications for day and month.

My week is from Monday to Sunday.

Time intelligence is on (I tried both on and off).

The 'Date'[WeekNbrYear] is a calculated column from my date table. Data type is text and ranges from Monday to Sunday:

WeekNbrYear = "W" & WEEKNUM ('Date'[Date],2) & " - " & YEAR ([Date])

The below visual shows data for 2022 BUT the visual Card showing 1282 customers (W9-2022) instead of the latest being 356.

What am I doing wrong?

Re: Count the latest week of data available.

vapid128 — Sat, 16 Jul 2022 00:38:45 GMT

Latest week count =

VAR Count = MAX('Date'[WeekNbrYear])RETURN

CALCULATE(COUNT('Fact Table'[Entry_date]),('Date'[WeekNbrYear] = Count))

VAR Count = MAX('Date'[WeekNbrYear])

means the max number of whatever you select

if you want the max number without any select,

VAR Count = calculate(MAX('Date'[WeekNbrYear]),all(table))

Re: Count the latest week of data available.

matrix_user — Sat, 16 Jul 2022 01:22:53 GMT

Not sure what you are saying... do you mean I should change my current DAX expression to:

Count = calculate(MAX('Date'[WeekNbrYear]),all(table)),

CALCULATE(COUNT('Fact Table'[Entry_date]),('Date'[WeekNbrYear] = Count))

...because that doen't work.

Re: Count the latest week of data available.

vapid128 — Sat, 16 Jul 2022 01:30:29 GMT

Try this

VAR Count =

CALCULATE(

COUNT('Fact Table'[Entry_date]),

FILTER(All('Fact Table'),'Date'[WeekNbrYear] = MAX('Date'[WeekNbrYear]))
)

Re: Count the latest week of data available.

matrix_user — Sat, 16 Jul 2022 03:26:10 GMT

Hello,

Still not quiet working. The 'WeekNbr_Year" is greyed out and an error message is saying something along the lines of needing an aggreagtion.

Weekly count = CALCULATE(COUNT('Fact Table'[Entry_date]),

FILTER(ALL('Fact Table'),'Date'[WeekNbr_Year] = MAX('Date'[WeekNbr_Year])))

A single value for column 'WeekNbr_Year' in table 'Date' cannot be detewrmined. This can happen when a measure formula refers to a column that contains amny values without specifying an aggregation such as min,max,count, or sum to get a single result.

Re: Count the latest week of data available.

v-zhangti — Wed, 20 Jul 2022 09:22:18 GMT

Hi, matrix_user

Can you provide some sample data or simple pbix files? Sensitive information can be removed in advance.

Best Regards,

Community Support Team _Charlotte

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

Re: Count the latest week of data available.

vapid128 — Wed, 20 Jul 2022 17:31:54 GMT

OK,

Make a [wwekNbr_year] colnum in fact table,

Weekly count = CALCULATE(COUNT('Fact Table'[Entry_date]),

FILTER(ALL('Fact Table'),'Fact Table'[WeekNbr_Year] = MAX('Fact Table'[WeekNbr_Year])))