Line Chart help

[Rsanjuan]

Hi,

I want to display the progress for a Month-to-date Line chart, similar to what I did below for the YTD chart:

But instead for the MTD chart, I want to display the 4 weeks of that month on the x-axis.  How can I go about doing this?

So far, I have this:  BidWeek = WeekNum(Bids[CreatedDate].[Date])

But it's displaying all of the weeks in the year.  I just want it for the current month.  Also, for a Week-to-date chart, I would like to see the progress on a daily basis with the start of the week on Sunday,  how do I create a dax expression to only show for the current week where the days are listed, Monday, 9/19, Tuesday, 9/20, etc.?

Thanks!

[Anonymous]

Hi @Rsanjuan,

In your scenario, you can create a separate Date table, create relationship using Date columns between Date table and your current table. Then you can create calculated columns in the Date table using the following formals.  

1. Use the following formula to create a MTD calculated column.
MTD = IF(MONTH('Date'[Date])=Month(NOW()), "Current Month", Format([Date], "YYYY mmmm"))

2. Use the following formulas to create a WTD calculated column.
IsInCurrentYear = if(YEAR(NOW())= [Year],1,0)
WeekOfYearNumber = WEEKNUM([Date])
WTD = if([isInCurrentYear] && WEEKNUM(NOW())=[WeekOfYearNumber],"CurrentWeek",Format([Date], "YYYY mmmm"))

3. Use the above calculated columns in slicer to filter your Line chart.


For more details, you can review the example in  this attached PBIX file.

Thanks,
Lydia Zhang