Create measure or table with weekly trends from a dataset with daily snapshot

[diogobraga2]

I need to create a weekly (every Monday) trend visual from a dataset with daily snapshots. I am wondering if it is best to create a measure or a table with weekly snapshots since there will be multiple measures that have daily snapshot, but will be displayed in weekly trends. 

Sample pbix 

[mahoneypat · Accepted solution]

Have you tried to wrap your measure(s) in an IF to get your desired result?  For example, using the Date column used in your visual -

Just Mondays = IF(WEEKDAY(MIN(Table[DateColumn]))=1, [YourMeasure])

This will return blank and get filtered out from the visuals on all days except Monday.

If this works for you, please mark it as the solution.  Kudos are appreciated too.  Please let me know if not.

Regards,

Pat

[diogobraga2]

Thanks! The measure had to be aggregated for it to work.

Just Mondays = IF(WEEKDAY(MIN('Dates Adj'[Date]))=1, sum([Total Returning]))

🙂

[Icey]

Hi @diogobraga2 ,

My understanding is this:

You want to show week’s data in the visual.

If my understanding is wrong, please let me know. And I hope you can provide a more detailed description, preferably in the form of icons or screenshots, thanks.

If my understanding is right, try this:

1. Use unpivot in the Power Query Editor. Select the School Code and Date columns and Click Unpivot Other Columns.

2. Create a calculated column in your Dates Adj table which is to calculate the number of weeks. The “2” in the WEEKNUM function means week begins on Monday.

Week Number1 = FORMAT(WEEKNUM([Date],2),"00")

3. You can put them in the matrix. The result is as below. It shows weekly data changes.

You can check more details from here.

Best Regards,

Icey

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

[diogobraga2]

Thank you @Icey !

Your result is summing each week's values per attribute, that's not the desired outcome. The goal is to extract the data from each Monday as a representation of the week's total. The values inside the yellow boxes would represent the first and second weeks in the weekly trend visual.