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

The ultimate Microsoft Fabric, Power BI, Azure AI & SQL learning event! Join us in Las Vegas from March 26-28, 2024. Use code MSCUST for a $100 discount. Register Now

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
Fabric Community Conference

Microsoft Fabric Community Conference

Join us at our first-ever Microsoft Fabric Community Conference, March 26-28, 2024 in Las Vegas with 100+ sessions by community experts and Microsoft engineering.

February 2024 Update Carousel

Power BI Monthly Update - February 2024

Check out the February 2024 Power BI update to learn about new features.

Fabric Career Hub

Microsoft Fabric Career Hub

Explore career paths and learn resources in Fabric.

Fabric Partner Community

Microsoft Fabric Partner Community

Engage with the Fabric engineering team, hear of product updates, business opportunities, and resources in the Fabric Partner Community.

Top Solution Authors
Top Kudoed Authors