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
thinkinfox
New Member

Slicing for strange month ends

We run a strange month, it's the last Monday of the previous month to the last Sunday of the current month (so October would be 25th Sept 2022 to 31 October 2022).

 

This means that slicing doesn't work nicely for months in my scenario using the date column in my main table. I'm trying to build two visuals: one that shows data for current month and one that shows it by month for the year (remember these are my strange months). I have data in my main table (Time Sheets) that I'm summing/etc:

thinkinfox_0-1668035189066.png

and a date table (you can see my strange months):

thinkinfox_1-1668035303585.png

I can't seem to:

  1. create a slicer that would select 'Fiscal Month - 10' and then just use that data in the main/Time Sheets table that's in that date (it's a 1:Many)
  2. Create a chart of Fiscal Month (from Data Table) with the sum of hours from the Time Sheets.

Have I sent myself down a rabbit hole/is there another way to solve this?

1 REPLY 1
amitchandak
Super User
Super User

@thinkinfox , Try a measure like below, it is based on today

 


Measure = //Current month is nov
var _date = eomonth(today(),-1)
var _mind = eomonth(today(),-2)
var _min = _mind -weekday(_mind,2)+1
return
calculate(Sum(Table[Value]), filter(Table, Table[Date] >=_min && Table[Date] <=_max))

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.