cancel
Showing results for 
Search instead for 
Did you mean: 
Reply

MTD without using a date table

When creating smaller reports; I add year and month columns to the existing dataset according to whatever the "DATE" field is. 

 

Ex:

DATE            YEAR      MONTH

1/2/2017      2017          1

1/3/2017      2017          1

3/5/2016      2016          3

6/8/2016      2016          6

7/28/2016    2016          7

7/25/2016    2016          7

7/25/2017    2016          7

 

I am creating visualizations, and I want them to only show MTD. ( So the 7/28/2016 data will not be on the visual )

How do I do this?

 

1 ACCEPTED SOLUTION
Super User I
Super User I

If you can work with Current Week or Prevoius Weeks, this might work... 

 

I've added 2 calculated columns and 1 measure to your data to accomplish (by week) your desired filtering.  WeekNum will return a number 1-52 of the week from your date.  Using it again with TODAY() returns today's weekNum.  The Calculated Column is then used in a filter to only show values with WEEKNUM less than or equal to TODAY()'s weekNum.

 

 

Measure:

TodaysWeek = WEEKNUM(TODAY())

 

Cal. Colmuns:

Week = WEEKNUM(Table1[DATE        ])

Display = IF(Table1[Week] <= [TodaysWeek],1,0)

*** If you don't want current week, only previous weeks, change <= to < ***

 

 

Capture.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

2 REPLIES 2
Super User I
Super User I

If you can work with Current Week or Prevoius Weeks, this might work... 

 

I've added 2 calculated columns and 1 measure to your data to accomplish (by week) your desired filtering.  WeekNum will return a number 1-52 of the week from your date.  Using it again with TODAY() returns today's weekNum.  The Calculated Column is then used in a filter to only show values with WEEKNUM less than or equal to TODAY()'s weekNum.

 

 

Measure:

TodaysWeek = WEEKNUM(TODAY())

 

Cal. Colmuns:

Week = WEEKNUM(Table1[DATE        ])

Display = IF(Table1[Week] <= [TodaysWeek],1,0)

*** If you don't want current week, only previous weeks, change <= to < ***

 

 

Capture.PNG



Did I answer your question, or help you along the way?
Please give Kudos or Mark as a Solution!


Proud to be a Super User helping give back to the community!
Thank You!




View solution in original post

Helper I
Helper I

Add a measure:

 

MTD = NOW()

 

Then use this measure to your visuzlization as a filter where date column < MTD.

Helpful resources

Announcements
secondImage

Happy New Year from Power BI

This is a must watch for a message from Power BI!

December Update

Check it Out!

Click here to read more about the December 2020 Updates!

Community Blog

Check it Out!

Click here to read the latest blog and learn more about contributing to the Power BI blog!

Get Ready for Power BI Dev Camp

Get Ready for Power BI Dev Camp

Mark your calendars and join us for our next Power BI Dev Camp!.

Top Solution Authors
Top Kudoed Authors