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
doubles
Helper I
Helper I

Compare current day to previous day to identify new data

I have a table of issues that are tagged with a date so I can show the number of issues over time, but I also want to show the number of new issues per day. I thougt I could do this using a measure with EXCEPT, where i would just compare the list of issues from the current day to the list from teh previous day and use COUNTROWS to display the differences. I am, however, having trouble getting the previous day table to return anything but blank in the EXCPET formula. I attached some screenshots/code. To clarify, the number being returned by the Test measure is just the count of issues for the current day, since the prevday table is empty. Any help would be appreciated!Data model.PNGTest.PNGtest2.PNG

Test = 
VAR prevday =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDatePrevDay] ),
            "Issue", IssueTracking[Issue]
        )
    )
VAR currday =
    DISTINCT (
        SELECTCOLUMNS (
            FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDate] ),
            "Issue", IssueTracking[Issue]
        )
    )
VAR compare =
    EXCEPT ( currday, prevday )
VAR difference =
    COUNTROWS ( compare )
RETURN
    IF ( [SelectedDate] = [MinIssueDate], BLANK (), difference )
Test2 = 
VAR prevday = 
DISTINCT (
            SELECTCOLUMNS (
                FILTER ( IssueTracking, IssueTracking[IssueDate] = [SelectedDatePrevDay]),
                "Issue", IssueTracking[Issue]
            )
)


var difference = COUNTROWS( prevday )
RETURN
 difference
 
SelectedDate = FIRSTNONBLANK(Dates[Date], TRUE())
SelectedDatePrevDay = FIRSTNONBLANK(Dates[Date], TRUE()) - 1

 

3 REPLIES 3
MFelix
Super User
Super User

Hi @doubles 

 

Try to remove the bidirectionality from your relationship between the dates table and the IssueTracking table.

 

If doesnt' work can you share a sample of the data?

 

Regards,

MFelix


Regards

Miguel Félix


Did I answer your question? Mark my post as a solution!

Proud to be a Super User!

Check out my blog: Power BI em Português



@MFelix -- Thank you for the reply, changing the cardinality didn't work. I put some sample data at the link below.

 

https://drive.google.com/open?id=1JpbzQsfDl81FoSD9bvAUGuQus0NE4Hhb

 

I solved this using calcualted tables, however would still be curious to see if anyone knows how to do it with a 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.