Visual showing Months before and after dates within my fact table

DAX DAX Calculations
1

I have nothing on my fact table before Sep 2019, and nothing after November 2020. I’ve added a column to my date table:

DatesWithCases = AND(Dates[Date] <= MAX('SourceData'[createdatetime]),Dates[Date]>=MIN('SourceData'[createdatetime]))

I’ve confirmed on the data tab that the trues and falses are showing up correctly.

I have multiple date columns in my fact table, I’ve used USERELATIONSHIP() to indicate which relationship to use.

I’ve created a measure to calculate case performance which gives the correct numbers

SSAT (Closed Date) = CALCULATE(((([Top Boxes]-[Bottom Boxes])/[Rated Cases (Closed Date)])*100 +100),USERELATIONSHIP('SourceData'[ClosedDate],Dates[Date]),Dates[DatesWithCases]=TRUE())

Any time I visualize the data based on MonthInCalendar, the visual gives me Jan 2019 thru Dec 2022. What am I doing wrong here?

2

Hi @mickeydjw, we aim to consistently improve the topics being posted on the forum to help you in getting a strong solution faster. While waiting for a response, here are some tips so you can get the most out of the forum and other Enterprise DNA resources.

  • Use the forum search to discover if your query has been asked before by another member.

  • When posting a topic with formula make sure that it is correctly formatted to preformated text </>.

  • Use the proper category that best describes your topic

  • Provide as much context to a question as possible.

  • Include demo pbix file, images of the entire scenario you are dealing with, screenshot of the data model, details of how you want to visualize a result, and any other supporting links and details.

I also suggest that you check the forum guideline https://forum.enterprisedna.co/t/how-to-use-the-enterprise-dna-support-forum/3951. Not adhering to it may sometimes cause delay in getting an answer.

3

@mickeydjw

Can you please share your source and pbix file so the solution can be provided efficiently?

Thanks.

4

@mickeydjw Trying to make an educated guess, try this:

SSAT (Closed Date) =
CALCULATE (
    DIVIDE (
        [Top Boxes] - [Bottom Boxes],
        [Rated Cases (Closed Date)]
    ) * 100 + 100,
    USERELATIONSHIP ( 'SourceData'[ClosedDate], Dates[Date] ),
    KEEPFILTERS (
        Dates[DatesWithCases]  = TRUE ()
    )
)
5

I’ll see if I can find some sample data that is close enough. Can’t share the data or the .pbix, which is why I tried to give as much detail as I could.

6

@mickeydjw

Did you try the above solution provided by @AntrikshSharma ?

7

Unfortunately this didn’t solve the issue.

8

One of my coworkers reminded me of a solution to the problem that I helped them solve previously. Because my formula functioned properly for reporting the SSAT, but the formula caused a value without cases, I wrapped the measure into an IF statement. MonthlySSAT = IF(SSAT <> 100, SSAT). This removed any values that were reported as 100, and the visual worked properly.

9

@mickeydjw

Is the following measure working properly as you are comparing Dates[Date] which is in Date Format to SourceData’[createdatetime] which is in Date/Time Format.

10

Yes. DatesWithCases is a column I’ve added to my date table to give me a true/false value for filtering.

11

@mickeydjw

Try this and let me know what you get:

SSAT (Closed Date) =

VAR Calculation =
CALCULATE (
( ( ( [Top Boxes] - [Bottom Boxes] ) / [Rated Cases (Closed Date)] ) * 100 + 100 ),
USERELATIONSHIP ( ‘SourceData’[ClosedDate], Dates[Date] )
)
VAR MaxDate =
MAX ( ‘SourceData’[ClosedDate] )
VAR CurrentDate =
SELECTEDVALUE ( Dates[Date] )
VAR Result =
IF ( CurrentDate > MaxDate, BLANK (), Calculation )
RETURN
Result

12

Didn’t change it. I think the underlying issue is that even if there are no results in that month, the calculation still gives a value of 100 which is causing the headaches.

13

@mickeydjw
I believe the solution is not that difficult but unable to solve it as don’t have access to the data. Here is the video showing how you can mask sensitive data and then probably you can share the pbix file.

Thanks.

14

Thanks, this video is great info for the future and I’ll use it if I have something else to solve. Since I was able to work around my issue with an IF() statement, I’m moving on to other problems. Thank you for your help.

1 Like
15

@mickeydjw Can you tell us what you did to solve it? Just curious :nerd_face:

16

image
image898×653 46.4 KB

1 Like