CODEWITHSUNDEEP
powerbidaxpowerbi-desktop
Brandon Dyer-Smith
Brandon Dyer-Smith

Reputation: 1

Power BI KPI Visual

In PowerBI Desktop, I have a KPI measure in which I am wanting to compare, for example, total number of items sold this month up until today, to total number of items sold last month up until todays date but I am struggling with what to return in DAX for last month up until todays date.

I used both of these measures:

Previous MTD ItemQty = 
CALCULATE(
    [MTD ItemQty], 
    DATEADD(vw_OrderItemWithProductDetail[orderplacementdate].[Date], -1, MONTH)
)

Previous MTD ItemQty Blank Future = 

VAR CurrentDate = MAX(vw_OrderItemWithProductDetail[orderplacementdate].[Date])

VAR PreviousMTDItemQty = 
CALCULATE(
    [MTD ItemQty], 
    DATEADD(vw_OrderItemWithProductDetail[orderplacementdate].[Date], -1, MONTH)
)

RETURN
IF (
    CurrentDate > TODAY(), 
    BLANK(), 
    PreviousMTDItemQty
)

However the first one returns the full months item quantity, and the second one returns blank on the KPI visual specifically because the last value that is returned is blank. I am struggling to stop returning once the previousMTD has reached todays date last month.

Upvotes: 0

Views: 93

Answers (1)

Sam Nseir
Sam Nseir

Reputation: 12111

Try it without the .[Date]...

Previous MTD ItemQty = 
  CALCULATE(
    [MTD ItemQty], 
    DATEADD(vw_OrderItemWithProductDetail[orderplacementdate], -1, MONTH)
  )

Here's an example:
enter image description here

ThisMTD = 
  CALCULATE( MIN(DimDate[Date]) & " - " & MAX(DimDate[Date]), DATESMTD(DimDate[Date]) )

PreviousMTD = 
  CALCULATE([ThisMTD], DATEADD(DimDate[Date], -1, MONTH))

Upvotes: 0

Related Questions