Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

The Power BI DataViz World Championships are on! With four chances to enter, you could win a spot in the LIVE Grand Finale in Las Vegas. Show off your skills.

Reply
sriramk
Helper I
Helper I

Daily Sales Report handling , DAX for deviation for dynamic date range

Please help me to solve the below:

 

I will receive daily sales report in excel workbook (.xlsb) with multiple sheets. Column names, Sheet names and structure is same but file name changes daily and sample can be seen below:

 

Daily Report_26/04/2022

Daily Report_27/04/2022 and so on.

 

How can I do query to add only the latest data (sales of 27/04/2022) without refreshing old data ie., check for the latest date and if date does not exist add the new data.

 

Is there a better way other than using “Delete duplicates” as it is taking long time for loading many excel workbooks(.xlsb) and sometimes it is unable to load to “Transformation” also. (FYI, the latest excel workbook contains along with historical details with the latest one)

 

Also help me with DAX, to compare day sales with previous average sales for dynamic date ranges (viz., one day/two days/three days/week/month/quarter/Trailing Twelve Month etc.,) and check for deviation. If negative deviation is more than 5%, then Status should show as “Poor” else for positive deviation the status is “Good”.

 

 

sriramk_0-1651038840138.png

 

1 ACCEPTED SOLUTION
v-yingjl
Community Support
Community Support

Hi @sriramk ,

For Date modified, you can refer @ ManuMMI 's post, about dax in this thread, if you do not want to change the formula frequently and achieve it dynamically, suggest that you can create a calendar table first and use it as a slicer under date slider so that you can change the date range as your need.

Table = CALENDARAUTO()

Create a measure to calculate the average:

Dyanamics last three days average = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Status Table'[Date] ) >= _mindate
            && SELECTEDVALUE ( 'Status Table'[Date] ) <= _maxdate,
        CALCULATE (
            AVERAGE ( 'Status Table'[Sales] ),
            FILTER (
                ALL ( 'Status Table' ),
                'Status Table'[Date] >= _mindate
                    && 'Status Table'[Date] <= _maxdate
            )
        ),
        BLANK ()
    )

Create another measure to show the status:

Status = 
VAR _deviation =
    DIVIDE (
        SELECTEDVALUE ( 'Status Table'[Sales] ) - [Dyanamics last three days average],
        SELECTEDVALUE ( 'Status Table'[Sales] )
    )
RETURN
    IF (
        [Dyanamics last three days average] <> BLANK (),
        IF (
            _deviation < 0
                && ABS ( _deviation ) < 0.05,
            "poor",
            IF ( _deviation > 0, "good" )
        )
    )

If you also want to show the background in the status field, you can create an extra conditional format measure and apply it for the [Status] measure:

Conditional format background = 
SWITCH ( TRUE (), [Status] = "poor", "red", [Status] = "good", "green" )

vyingjl_0-1651477313605.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

View solution in original post

3 REPLIES 3
v-yingjl
Community Support
Community Support

Hi @sriramk ,

For Date modified, you can refer @ ManuMMI 's post, about dax in this thread, if you do not want to change the formula frequently and achieve it dynamically, suggest that you can create a calendar table first and use it as a slicer under date slider so that you can change the date range as your need.

Table = CALENDARAUTO()

Create a measure to calculate the average:

Dyanamics last three days average = 
VAR _maxdate =
    CALCULATE ( MAX ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
VAR _mindate =
    CALCULATE ( MIN ( 'Table'[Date] ), ALLSELECTED ( 'Table' ) )
RETURN
    IF (
        SELECTEDVALUE ( 'Status Table'[Date] ) >= _mindate
            && SELECTEDVALUE ( 'Status Table'[Date] ) <= _maxdate,
        CALCULATE (
            AVERAGE ( 'Status Table'[Sales] ),
            FILTER (
                ALL ( 'Status Table' ),
                'Status Table'[Date] >= _mindate
                    && 'Status Table'[Date] <= _maxdate
            )
        ),
        BLANK ()
    )

Create another measure to show the status:

Status = 
VAR _deviation =
    DIVIDE (
        SELECTEDVALUE ( 'Status Table'[Sales] ) - [Dyanamics last three days average],
        SELECTEDVALUE ( 'Status Table'[Sales] )
    )
RETURN
    IF (
        [Dyanamics last three days average] <> BLANK (),
        IF (
            _deviation < 0
                && ABS ( _deviation ) < 0.05,
            "poor",
            IF ( _deviation > 0, "good" )
        )
    )

If you also want to show the background in the status field, you can create an extra conditional format measure and apply it for the [Status] measure:

Conditional format background = 
SWITCH ( TRUE (), [Status] = "poor", "red", [Status] = "good", "green" )

vyingjl_0-1651477313605.png

 

Best Regards,
Community Support Team _ Yingjie Li
If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

 

Hi @v-yingjl ,

Thank you for your support!

 

Could you also please also help me how to convert from (.xlsb) file to (.xlsx) file automatically, since I receive .xlsb files daily from our client.

ManuMMI
Regular Visitor

You should try to filter the file for "Date modified "  on Power Query 

Some ideas considering I have 2 Excel Files and the last has all information 

let
Source = Folder.Files("C:\Users\Usuario\Desktop\Proyectos\Power BI\multiples"),
#"Removed Other Columns" = Table.SelectColumns(Source,{"Content", "Date modified"}),
#"Filtered Rows" = Table.SelectRows(#"Removed Other Columns", let latest = List.Max(#"Removed Other Columns"[Date modified]) in each [Date modified] = latest),
#"Added Custom" = Table.AddColumn(#"Filtered Rows", "personal_column", each Excel.Workbook([Content])),
#"Removed Columns" = Table.RemoveColumns(#"Added Custom",{"Content"}),
#"Expanded {0}" = Table.ExpandTableColumn(#"Removed Columns", "personal_column", {"Name", "Data", "Item", "Kind", "Hidden"}, {"Name", "Data", "Item", "Kind", "Hidden"}),
#"Removed Other Columns1" = Table.SelectColumns(#"Expanded {0}",{"Data", "Date modified"}),
#"Expanded {0}1" = Table.ExpandTableColumn(#"Removed Other Columns1", "Data", {"Column1", "Column2"}, {"Column1", "Column2"}),
#"Promoted Headers" = Table.PromoteHeaders(#"Expanded {0}1", [PromoteAllScalars=true]),
#"Changed Type" = Table.TransformColumnTypes(#"Promoted Headers",{{"date", type date}, {"sales", Int64.Type}, {"28-04-2022 3:07:31", type datetime}})
in
#"Changed Type"

Helpful resources

Announcements
Las Vegas 2025

Join us at the Microsoft Fabric Community Conference

March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!

Feb2025 Sticker Challenge

Join our Community Sticker Challenge 2025

If you love stickers, then you will definitely want to check out our Community Sticker Challenge!

Feb2025 NL Carousel

Fabric Community Update - February 2025

Find out what's new and trending in the Fabric community.

Top Solution Authors
Top Kudoed Authors