How to create a group with dates in weeks

[Anonymous]

Hi guys,

I would like to create a date group to be able to have a hierarchy with dates in this order :

Year,

Month,

Week,

Day

And be able to use this to surf into dates data. It's possible to do it by month, it's possible to group 7 days together but without chossing the starting day to ensure it's the first day of the week...

Any idea ?

Today I use the WEEKDAY formula to create a Weeks column but when i have a cumulated measure I have to choose between Weeks and Dates so it's less intuititve... Any help ? 🙂

[v-jiascu-msft]

Hi @Anonymous,

You can choose the first day of the week with the function Weeknum. 

WeekNum2 = WEEKNUM([Date],2)

You can try it as the picture showed. Keep Year and Month of dates, then add weeknum and day. You can try it in this file: https://1drv.ms/u/s!ArTqPk2pu-BkgTe6DDZzP5BIAdKG

Best Regards!

Dale

[Anonymous]

Thank you for your answer but i would like to have a cumulated measure that can be showcased with a axis that can be set to date, weekn, month or year.

As you cant choose two fields for a cumulated measure, your solution doesnt work, do you have an other idea ?

Thanks anyway 🙂

[v-jiascu-msft]

Hi @Anonymous,

Can you share a sample .pbix file? 

An axis that can be set to date, weeknum, month or year has nothing to do with a measure. You can set what you want on an axis. The measure will response to the context that the axises provide.

Best Regards!

Dale

[Anonymous]

Of course, here it is : https://we.tl/s-QZT2l3aYgl

In this example the cumulated measure works for the dates when used by day but when I try to have the week or the month, the cumulated measure doesnt work and i have a simple production sum...

[v-jiascu-msft · Accepted solution]

Hi @Anonymous,

I think you'd better create a independent table of "production". I made some changes based on your data. 

1. A new measure.

Total cumulé de Production dans Date 2 =
CALCULATE (
    SUM ( 'Feuil1'[Production] ),
    FILTER ( ALL ( 'Feuil1' ), 'Feuil1'[Date] <= MAX ( 'Feuil1'[Date] ) )
)

2. The axis could be day instead of another date. 

The file is here: https://1drv.ms/u/s!ArTqPk2pu-BkgTjEU0B2_AmmLREa 

Best Regards!

Dale

[Anonymous]

It did work, thanks a lot 🙂