Register now to learn Fabric in free live sessions led by the best Microsoft experts. From Apr 16 to May 9, in English and Spanish.
This is regarding automated reporting for sales tec. using RELATIVE filter. No Month filter
We have a calendar that has future dates. I've never had to work with that. At my last job Microsoft consultants made us a calendar that stopped the prior days date. In fact, they had two calendars and one did have future dates.
We report effective YESTERDAY. That becomes more of an issue the first day of the month.
So I subtracted 1 from TODAY().
This DAX works:
This and none of the calculations in the Calendar table does this work. It still is returning the new current month number. (1 example):
IsWorkingDayCompleted = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),
Then I try this and it won't work:
Error message is A function 'CALCULATE' has been used in a TRUE/FALSE expression that is used as a filter expression. This is not allowed.
I assume it is the [BI_Calendar[IsWorkingDay_NUM] and that returns a number (1 for each day).
Calculated as: IsWorkingDay_NUM = IF( BI_Calendar[IsWorkingDay] = TRUE() , 1,0 )
Hi @Anonymous
The error message you get is easy to fix but I do not quite get what the overall problem is and waht you intend to do. Could you plase explain it a bit more, ideally basing the explanation on sample data?
Please mark the question solved when done and consider giving kudos if posts are helpful.
Contact me privately for support with any larger-scale BI needs, tutoring, etc.
Cheers
The issue is: The Calendar has future dates but we report as of yesterday. The big problem is when it is the first day of the month, say October 1. We are reporting September and the calendar tells the report it is October.
I can't figure out how to tell measures to look back 1 day.
I wonder if a simple solution is to make a column in Calendar that is Accounting_Date and have that one day behind. I don't know how to do that. I know how to make calendar columns but not one that is one day behind.
@Anonymous
I am not sure with the following filter expression you provided:
On the first day of the month I want to report last month. The issue is this formula I expected look to September on Oct 1 and it did not ( [IsWorkingDay_NUM] = 1 for a working day):
This calculates the number of working days worked in the reported month
IsWorkingDayCompleted = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),
Instead of = Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),
= Calculate( SUM(BI_Calendar[IsWorkingDay_NUM] ),
Covering the world! 9:00-10:30 AM Sydney, 4:00-5:30 PM CET (Paris/Berlin), 7:00-8:30 PM Mexico City
Check out the April 2024 Power BI update to learn about new features.
User | Count |
---|---|
97 | |
96 | |
81 | |
74 | |
66 |
User | Count |
---|---|
126 | |
106 | |
105 | |
86 | |
72 |