PL-300: Microsoft Power BI Data Analyst
You have a Power BI model that contains tables named Sales and Date. Sales contains four columns named SalesAmount, OrderDate, SalesPerson, and OrderID.
You need to create a measure to calculate the last 12 months of sales. You must start from the last date a sale was made and ignore any filters set on the report.
How should you complete the DAX formula?
Answer is DATEADD and LASTNONBLANK
1. get the date 12 months ago: DateAdd
2. get the valid last date: LastNonBlank
3. nested above 2 together by “Calculate”
Reference:
https://www.kasperonbi.com/show-only-the-sales-for-the-last-12-months/
https://docs.microsoft.com/en-us/dax/dateadd-function-dax
https://docs.microsoft.com/en-us/dax/lastnonblank-function-dax
https://docs.microsoft.com/en-us/dax/calculate-function-dax
You have a Power BI model that contains a table named Person. Person contains a whole number column named Age.
You need to write a DAX measure that finds the middle value in the range of Age values.
Which two formulas should you use? Each answer presents a complete solution.
MEDIAN(‘Person’[Age])
PERCENTILE.INC((‘Person’[Age], 0.5)
AVERAGE(‘Person’[Age])
RANK.EQ(‘Person’[Age], ‘Person’[Age])
You have a Power BI model that contains the following two tables:
- Assets (AssetID, AssetName, Purchase_DateID, Value)
- Date (DateID, Date, Month, Week, Year)
The tables have a relationship. Date is marked as a date table in the Power BI model.
You need to create a measure to calculate the percentage that the total assets value increased since one year ago.
Which DAX formula should you use?
(sum(Assets[Value]) – CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))
CALCULATE(sum(Assets[Value]), SAMEPERIODLASTYEAR("˜Date'[Date]))/ (sum(Assets[Value])
CALCULATE(sum(Assets[Value]),DATESYTD(("˜Date'[Date]))/ (sum(Assets[Value])
sum(Assets[Value]) / CALCULATE(sum(Assets[Value]), SAMEPERIODLASTYEAR('Date'[Date]))
Answer is (sum(Assets[Value]) – CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR(‘Date’[Date])))/CALCULATE(sum(Assets[Value]),SAMEPERIODLASTYEAR (‘Date’ [Date]))
basis formula is % = (current/prior - 1) = (current - prior) / prior
You need to write a DAX measure that returns the revenue for selected dates during the previous year.
Which two DAX functions should you include in the measure? Each correct answer presents part of the solution.
CALCULATE
CLOSINGBALANCEYEAR
PREVIOUSQUARTER
SAMEPERIODLASTYEAR
Answers are;
CALCULATE
SAMEPERIODLASTYEAR
You need to create a role-playing dimension using DAX. Which type of DAX expression should you use?
Calculated column
Calculated table
Measure
What-If analysis
Answer is Calculated table
You have a Microsoft Power BI semantic model that contains two tables named DimDate and FactInventory. DimDate is the date table. FactInventory is a snapshot table that contains one row per product per date with the count of items remaining in inventory.
You need to create a measure named Stock on Hand that returns the number of items available in inventory as of the last day of the selected period. The measure must work in the context of a specific day, week, month, or year.
Which formula should you use in the measure?
CALCULATE( SUM(FactInventory[UnitsBalance]), LASTDATE('Date'[Date]) )CALCULATE( SUM(FactInventory[UnitsBalance]), LASTNONBLANK('Date'[Date], CALCULATE(SUM(FactInventory[UnitsBalance])) ) ) CALCULATE( SUM(FactInventory[UnitsBalance]), FIRSTDATE('Date'[Date]) )CALCULATE( SUM(FactInventory[UnitsBalance]), MAX('Date'[Date]) )Answer is
CALCULATE( SUM(FactInventory[UnitsBalance]), LASTDATE('Date'[Date]) )
You have a Microsoft Power BI semantic model that contains two tables named FactInventory and DimDate. FactInventory is a fact table that contains one row per product per weekday. Inventory balances are NOT recorded on weekends. DimDate is the date table in the model.
You need to create a measure that shows the last available inventory balance for a selected period.
Which DAX function should you include in the solution?
EOMONTHFIRSTNONBLANKLASTNONBLANKNETWORKDAYSAnswer is
LASTNONBLANK
You have a Microsoft Power BI report that contains a bar chart showing sales by month. The chart is populated by adding a column named Sales[SalesAmt] to the visual and setting the summarization to Sum.
You need to replace the field in the visual with an explicit measure.
Which measure should you create?
Sales Amount = COUNT(Sales[SalesAmt]) Sales Amount = SalesSales Amount = Sales[SalesAmt] Sales Amount =SUM(Sales[SalesAmt]) Answer is
Sales Amount =SUM(Sales[SalesAmt])
You have a Power BI model that contains a table named Person. The Person table contains a whole number column named Age.
You need to write a DAX measure that finds the middle value in the range of Age values.
What formula should you use?
AVERAGE('Person'[Age]) MEDIAN('Person'[Age]) RANK.EQ('Person'[Age], ‘Person’[Age]) STDEV.P('Person'[Age]) Answer is
MEDIAN('Person'[Age])
What benefit do you get from analyzing metadata?
The benefit of analyzing metadata is that you can clearly identify data inconsistencies with your dataset.
The benefit of analyzing the metadata is to get familiar with your data.
The benefit of analyzing the metadata is to know the number of rows, columns and tables being loaded into your model.
Answer is The benefit of analyzing metadata is that you can clearly identify data inconsistencies with your dataset.