Line chart to show weekinmonth

[Anonymous]

I want to show weekinmonth trend line for my data ( I have data from August - December) and I further drill it down to weekinmonth as per below ilustration. 

However, for December month, there were only 2 weeks of data. But the trend line shows 5 weeks. It can be seen on the right handside table that there are only 2 weeks in December month. How can I fix this in order for the line chart to show only 2 weeks data as per the dataset instead of 5. 

Could someone please help me with this. Thank you in advance. 

[az38]

Hi @Anonymous 

i think it's correct behaviour because your weekinmonth is a row not related to the exactly month and it aggreates data from the all weeknumber 3,4 and 5 from the data source.

to solve it try to create hierarchy which will include month and weekinmonth

do not hesitate to give a kudo to useful posts and mark solutions as solution

LinkedIn

[Anonymous]

Hi @az38 , 

the weekinmonth is actually a column that I create in power BI itself with the following formula 

weekinmonth = 1 + WEEKNUM ( FBL1N_trends[Posting_Date] )-WEEKNUM( STARTOFMONTH (FBL1N_trends[Posting_Date]))

[az38]

@Anonymous 

doesnt matter. try to insert this column into hierarchy

do not hesitate to give a kudo to useful posts and mark solutions as solution

[Anonymous]

Hi @az38 , 

I tried creating a hierarchy with month as below. But still it shows 5 weeks for December (12). 

Any idea how to solve this 

[az38 · Accepted solution]

@Anonymous 

it seems i got it. try to create a new field weekinmonth which will look like "Nov-1","Nov-2","Nov-3","Nov-4", "Dec-1","Dec-2", etc..

now its still aggregated by all model with no regards to month.

do not hesitate to give a kudo to useful posts and mark solutions as solution