cancel
Showing results for 
Search instead for 
Did you mean: 
Reply
Highlighted
Helper IV
Helper IV

DAX FUNCTION

Hi All,

 

My requirement is to show current month data as a KPI value

 

Sample Data1:

 

JAN   0.1

FEB    0.2

Mar   0.3

Apr    0.4

 

KPI Value = 0.4

 

Sample Data2:

 

JAN   0.1

FEB    0.2

Mar   0.0

Apr    0.0

 

KPI Value = 0.2

 

If the current month value is 0 , check prio month . If prior month is 0 then look for 2months back value and so on..

 

Thanks

 

 

2 ACCEPTED SOLUTIONS

Accepted Solutions
Highlighted
Memorable Member
Memorable Member

Re: DAX FUNCTION

Assuming that Table1 is your table name, the first column is of date type (and is called Date) and there is a measure called Sales = sum(Table1[KPIValue]), then use the formula below

 

Test =
VAR LastDateWithSales =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALL ( Table1 ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )
)
RETURN
CALCULATE ( [Sales], Table1[Date] = LastDateWithSales )

i got the below resulttemp.png

View solution in original post

Highlighted
Memorable Member
Memorable Member

Re: DAX FUNCTION

Is the Region coming from a master (lookup) table or is it part of the same Table? If part of the same table, you can just use an ALLEXCEPT

VAR LastDateWithSales =
CALCULATE (
MAX ( Table1[Date] ),
FILTER ( ALLEXCEPT ( Table1, Table1[Region] ), Table1[Date] <= MAX ( Table1[Date] ) && [Sales] > 0 )

This will ensure that the LastDAte is calculated after the filter on Region is done. If Region is coming from a lookup table, you can use the ALLEXCEPT for the field in Table1 that is connecting to the Region lookup table.  

View solution in original post

16 REPLIES 16
Highlighted
Memorable Member
Memorable Member

Re: DAX FUNCTION

It would be easier if you had given your model and table structure. 

 

What you have to do is to create a measure

 

KPI =
VAR MaxMonth =
    CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALL ( Table ), Table[Month] = MaxMonth )
    )

 

Highlighted
Super User VII
Super User VII

Re: DAX FUNCTION

You need to add a measure to you table with following formula and use myKPIValue measure to show the value.

 

myKPIValue = CALCULATE(MAX(Table1[myValue]), FILTER(ALL(Table1), Table1[myValue]>0))

 






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Helper IV
Helper IV

Re: DAX FUNCTION

Hi SqlJAson,

 

  what change needs to be done if i have to consider YEAR also.... MAX(table[month],table[year])?

 

KPI =
VAR MaxMonth =
    CALCULATE ( MAX ( Table[Month] ), FILTER ( ALL ( Table ), Table[Sales] > 0 ) )
RETURN
    CALCULATE (
        SUM ( Table[Sales] ),
        FILTER ( ALL ( Table ), Table[Month] = MaxMonth )
    )

 Thanks

 

Highlighted
Helper IV
Helper IV

Re: DAX FUNCTION

Hi Parry2k,

 

   we cant use max aggregation because value will not be in ascending order by month.

 

Thanks

Highlighted
Memorable Member
Memorable Member

Re: DAX FUNCTION

Ideally, there should  be one field in the table which is a combination of Year and Month (if not, you can create it by concatenating year and month). And then use that inside the max.

Highlighted
Super User VII
Super User VII

Re: DAX FUNCTION

True, I missed that part. In the case @SqlJason formula will work, Do you see any challenge with that?






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Super User VII
Super User VII

Re: DAX FUNCTION

That is where it is helpful to understand your tables, if you have continuous calendar table, it will help to make time based calculation easy. In case you don't have calendar table, here is the link on how you can create one and link your data table to calendar table.






Did I answer your question? Mark my post as a solution.

Proud to be a Super User! Appreciate your Kudos 🙂
Feel free to email me with any of your BI needs.





Highlighted
Community Champion
Community Champion

Re: DAX FUNCTION

Take your pick Smiley Happy

 

Month Order =
INT (
    CONCATENATE (
        YEAR ( 'Calendar'[Date] ),
        CONCATENATE (
            IF ( MONTH ( 'Calendar'[Date] ) < 10, "0", "" ),
            MONTH ( 'Calendar'[Date] )
        )
    )
)

 or the easier version

 

Month Order 2 = 
INT (
    CONCATENATE ( YEAR ( 'Calendar'[Date] ), FORMAT ( 'Calendar'[Date], "MM" ) )
)

or the easiest version Smiley Happy

 

Month Order 3 = 
VALUE ( FORMAT ( Calendar[Date], "YYYYMM" ) )

All 3 do the same! Smiley Happy

Highlighted
Memorable Member
Memorable Member

Re: DAX FUNCTION

Or even easier,
month order = VALUE(FORMAT(Calendar[Date], "YYYYMM"))

Helpful resources

Announcements

August Community Highlights

Check out a full recap of the month!

August 2020 CYST Challenge

Check out the winners of the recent 'Can You Solve These?' community challenge!

Experience what’s next for Power BI

Join us for an in-depth look at the new Power BI features and capabilities at the free Microsoft Business Applications Launch Event.

Top Solution Authors
Top Kudoed Authors