Find everything you need to get certified on Fabric—skills challenges, live sessions, exam prep, role guidance, and a 50 percent discount on exams.
Get startedEarn a 50% discount on the DP-600 certification exam by completing the Fabric 30 Days to Learn It challenge.
Hi Team,
below is my current measure
Hi
In what range should the dynamic part be? I.e. P01, P02,..., P12?
If this is the case then you should be able to do the following:
TER =
VAR __monthlyValidation = "P" & IF(MONTH(TODAY())<10;"0";"") & MONTH(TODAY())
RETURN
IF (
SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q2",
CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q2" )
- CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q1" ),
IF (
SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q1",
CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q1" ),
IF (
SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q3",
CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q3" )
- CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = "Q2" ),
CALCULATE ( [Gterrevenue], 'Account Listing'[Quarter] = __monthlyValidation )
)
)
)
Ps. it is a good idea to put your code through a DAX formatter to make it more readable (i.e. https://www.daxformatter.com/ )
If the above helps then please mark it as the solution, kudos is also appreciated.
@Anonymous
range order is something like this
P01, P02, Q1, P04, P05,Q2, P07, P08, Q3, P10, P11, P12
Let me know
Then you can probably change the measure to:
VAR __month =
MONTH ( TODAY () )
VAR __variable =
IF (
__month IN { 3, 6, 9 },
"Q" & ROUNDUP ( __month / 3, 0 ),
IF ( __month < 10, "P0" & __month, "P" & __month ))
@Anonymous
its throwing up some other errror, see below
here is a sample pbix
https://www.dropbox.com/s/boyrdc6kb7xdbdj/Real%20Estate%20FY20%20P02.pbix?dl=0
if you can help, it will be really great
My measure seems to work. But you only have data for P02?
The variable will return Q3 for the current date and everything is therefore 0, so it is tricky to validate.
TER = VAR __month = MONTH ( TODAY () ) VAR __variable = IF ( __month IN { 3; 6; 9 }; "Q" & ROUNDUP ( __month / 3; 0 ); IF ( __month < 10; "P0" & __month; "P" & __month ) ) RETURN IF ( SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q2"; CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q2" ) - CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q1" ); IF ( SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q1"; CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q1" ); IF ( SELECTEDVALUE ( 'Account Listing'[Quarter] ) = "Q3"; CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q3" ) - CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = "Q2" ); CALCULATE ( [Gterrevenue]; 'Account Listing'[Quarter] = __variable ) ) ) )
@Anonymous
that is how the data is
i have P02 now, next month i will have P03, which become Q1 and after that P05
& its the reason you see the quarter filter there, so that pepole can filter out the information based on selection they make.
so i assume the below measure will not help, if it returns "0"?
I cannot see where you can select a month in your workbook. But I guess that you can just change the __month to be for the month you have data for.
If that is not the case then I do not get your problem.