Wednesday, April 22, 2020

CALCULATE DIFFERENT MEASURES WITH DATE TABLE


i have used Adventureworks for all these examples.




How to calculate Total sale amount?


Total sales =
SUMX (
    FactInternetSales,
    FactInternetSales[SalesAmount] * FactInternetSales[OrderQuantity]
)



How to calculate YTD SALES?

Method1

TOTAL YEAR TO DATE SALES =

TOTALYTD ( [Total sales], DimDate[FullDateAlternateKey] )

Method2


TOTAL YTD USING CALCULATE FUNCTION =
CALCULATE ( [Total sales], DATESYTD ( DimDate[FullDateAlternateKey] ) )


How to calculate YTD SALES IF fiscal year doesn't

end with December 31?

 Method1


TOTAL YEAR TO DATE SALES =

TOTALYTD ( [Total sales], DimDate[FullDateAlternateKey],"06-30")


Method2


TOTAL YTD USING CALCULATE FUNCTION =
CALCULATE ( [Total sales], DATESYTD ( DimDate[FullDateAlternateKey] ,"06-30" )

How to get total sales for the previous year
for the same period?

 Method1


LAST YEAR SALES FOR the SAME PY =
CALCULATE (
    [Total sales],
  SAMEPERIODLASTYEAR ( DimDate[FullDateAlternateKey] )
)

Method2


TOTAL SALES LAST YEAR FOR THE SAME PY =
CALCULATE ( [Total sales], DATEADD ( DimDate[FullDateAlternateKey], -1YEAR ) )


Method3

Total sales using parallel period =
CALCULATE (
    [Total sales],
    PARALLELPERIOD ( DimDate[FullDateAlternateKey], -1YEAR )
)

No comments:

Post a Comment

Sort by Month and year in the table

  Solved: Sort by month in Power BI - Microsoft Power BI Community