Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
Anonymous
Not applicable

How to create a group with dates in weeks

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 ? 🙂

1 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 

How to create a group with dates in weeks2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

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

View solution in original post

6 REPLIES 6
v-jiascu-msft
Employee
Employee

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-BkgTe6DDZzP5BIAdKGHow to create a group with dates in weeks.JPG

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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 🙂

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

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

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...

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 

How to create a group with dates in weeks2.jpg

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Best Regards!

Dale

Community Support Team _ Dale
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
Anonymous
Not applicable

It did work, thanks a lot 🙂

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.