Forum Discussion
Display Weekly Total based on a report that is generated daily
I have report which is generated daily to CSV and then imported into SQL every day. The report has no date timestamp, so when the CSV is imported into SQL, it adds a datestamp column. I want to s...
Hi hatchda ,
According to my understanding, you want to calculate the distinct count of Machine of each Sunday,right?
You could firstly add a Week column and then use the following formula to calculate:
Week =
WEEKNUM ( [Date], 2 )Count Column=
CALCULATE (
DISTINCTCOUNT ( 'Table'[VMReport] ),
FILTER ( 'Table', 'Table'[Week] = EARLIER ( 'Table'[Week] ) )
)Or
Count Measure =
CALCULATE (
DISTINCTCOUNT ( 'Table'[VMReport] ),
FILTER ( ALL ( 'Table' ), 'Table'[Week] = MAX ( 'Table'[Week] ) )
)The final output is shown below:
Please kindly take a look at the pbix file here.
Best Regards,
Eyelyn Qin
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.