Forum Discussion

nhol's avatar
nhol
Advocate II
8 years ago

Compare and alert when data got changed

Hi,   I need a solution for the following query:   I have a daily dataset that on a regular basis don't change at all, however when the percentage of "CapitalPercent" between days are changing I ...
Need Help
v-jiascu-msft's avatar
v-jiascu-msft
Microsoft Employee
8 years ago

Hi nhol,

 

Maybe you can try something like this: adding an arrow when the data changes.

 

If you want the exact formula, please provide your pbix file or a sample.

Measure 2 =
VAR lastdayQuantity =
    CALCULATE (
        SUM ( FactSales[SalesQuantity] ),
        PREVIOUSDAY ( 'DimDate'[Datekey] )
    )
VAR sumQuantity =
    SUM ( FactSales[SalesQuantity] )
RETURN
    IF (
        lastdayQuantity > sumQuantity,
        CONCATENATE ( sumQuantity, UNICHAR ( 9660 ) ),
        IF (
            lastdayQuantity = sumQuantity,
            sumQuantity,
            CONCATENATE ( sumQuantity, UNICHAR ( 9650 ) )
        )
    )

Compare_and_alert_when_data_got_changed

 

 

Best Regards,

Dale

  • nhol's avatar
    nhol
    Advocate II
    8 years ago

    Hi Dale and thanks for your assistance.

    This worked just fine until I realized I don't have sequential dates as you can see from the picture attached (marked in yellow).

    The other challenge I'm having is that I'm using DirectQuery mode so any option of using a calculated column on the fly won't work here.

     

    Thanks!

    NH

    • nhol's avatar
      nhol
      Advocate II
      8 years ago

      Hi Dale,

       

      Did you have a chance to look at my last query regarding my DAX challenge.

      Your solution was perfect and served exactly what I've needed but there is a small issue with non sequential dates (skipping weekends). Any idea how to resolve this?

       

      Thanks!

      NH

      • v-jiascu-msft's avatar
        v-jiascu-msft
        Microsoft Employee
        8 years ago

        Hi nhol,

         

        If your mode is Direct Query to SQL Server, you can add a measure. You can make a little change to apply this measure to the non sequential dates.

        Measure =
VAR lastdayQuantity =
    CALCULATE (
        SUM ( FactSales[SalesQuantity] ),
        PREVIOUSDAY ( 'DimDate'[Datekey] )
    )
VAR sumQuantity =
    SUM ( FactSales[SalesQuantity] )
RETURN
    IF (
        ISBLANK ( lastdayQuantity ),
        sumQuantity,
        IF (
            lastdayQuantity > sumQuantity,
            CONCATENATE ( sumQuantity, UNICHAR ( 9660 ) ),
            IF (
                lastdayQuantity = sumQuantity,
                sumQuantity,
                CONCATENATE ( sumQuantity, UNICHAR ( 9650 ) )
            )
        )
    )

        Best Regards,

        Dale