Microsoft Fabric Community Conference 2025, March 31 - April 2, Las Vegas, Nevada. Use code MSCUST for a $150 discount.
Register nowThe 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.
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”.
Solved! Go to Solution.
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" )
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 @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" )
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.
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"
March 31 - April 2, 2025, in Las Vegas, Nevada. Use code MSCUST for a $150 discount!
If you love stickers, then you will definitely want to check out our Community Sticker Challenge!