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
lsullivan6311
Helper III
Helper III

Calculate measure based on month filter from visualization

My visualization will be for filtered for a given month.  

 

I need a measure that calculates units x Dates[daysinmonth] x 24  (hours in a day)

 

The daysinmonth needs to be determined based on the filtered month.  Can anyone help with this?

 

1 ACCEPTED SOLUTION

This solution has not worked for me, even using the MAX in the measure.

View solution in original post

8 REPLIES 8
Barthel
Solution Sage
Solution Sage

Hey @lsullivan6311

Create a calendar table where each row represents a date, with a month column that a user can select. You can do this in DAX, for example:

Calendar = 
ADDCOLUMNS ( 
    CALENDAR ( DATE ( 2022, 1, 1 ), DATE ( 2022, 12, 31 ) ),
    "Month",
    FORMAT ( [Date], "mmm yyyy" )
)

Create a relationship between the calendar table and your fact table. 

If the user has selected one month, you can easily calculate the number of days in that month by counting the number of rows in the calendar table:

No of days = 
COUNTROWS ( 'Calendar' )

I already have the Number of Months calculated column in my Dates table (this is not the measure I need) and I have a relationship between my fact table and the Dates table.  

I need the below measure to use the month filter from my visualization to come up with the no of days in the month.  The measure works if I just put in the number of days for the specific month I have filtered to, but I want the measure to be dynamic.

 

units x Dates[daysinmonth] x 24  (hours in a day)

@lsullivan6311 

 

To find the number of days in a selected month use the following formula in a new column:

No_of_Days= DAY(EOMONTH(Table[Date], 0))

 

So the final formula will be:

units x No_of_Days x 24 

No, does not work.

 

Hi @lsullivan6311 

 

You can try below measure for [No_of_Days].

 

No_of_Days = DAY ( EOMONTH ( MAX ( 'Date'[Date] ), 0 ) )

 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it. Highly appreciate your Kudos!

OK,

so I made a copy of my model, deleted my calendar table, and recreated it, added a new column in calendar for daysInMonth, and it is accounting for Leap years in February (Yeah!).  So now, the measure that was sent to me works.  Of course, I had to adjust ALL my old measures, but a half a day later and it is complete.  Thank you for the solution.

I tried both the MIN and MAX; however, it was giving me the correct answer for only a small percentage of my results, so I pulled in the No_of_Days measure to see what the number was getting produced and it was sometimes, 1,2,15, or 31 (where 31 was the correct answer, as I am filtering on the month of October for my test set).  I am wondering is my calendar table is causing me a problem as how I created it gives me a row for every day in the month.  For my model I do not think I will every need a single day.  All my data is rolled up by month (so essentially the data is end of the month data).

This solution has not worked for me, even using the MAX in the measure.

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.