Dax: convert monthly data to weeks

[Stharwani]

Hi team,

I am using this formula to show week number Weeks = ROUNDDOWN(('Date'[Date] - 'Date'[StartOfFY]+2)/7,0)+1 

When I try to show it along with months on a bar chart, the shared week appears twice. Is there any way to change this so it only appears once?

Thanks,

Sonali

[DAX0110]

Hi @Stharwani, the approach I'd suggest is to create another calculated column that computes the MAX(Month) for each Weeks value, then put that MAX(Month) column on the chart together with Weeks.

On the other hand, if you want to "snap" each Weeks value to the lower of the two months, use MIN(Month)

[Stharwani]

No, that did not work. 

[Stharwani]

Any ideas @ImkeF @MattAllington

Thanks. 

[MattAllington]

My advice is to create a proper calendar table. 
Read my article here  https://exceleratorbi.com.au/power-pivot-calendar-tables/ 

and my blog here https://powerpivotpro.com/2015/02/create-a-custom-calendar-in-power-query/