Skip to main content
cancel
Showing results for 
Search instead for 
Did you mean: 

Earn the coveted Fabric Analytics Engineer certification. 100% off your exam for a limited time only!

Reply
revansh
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

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

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
SqlJason
Memorable Member
Memorable Member

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 )
    )

 

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

 

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.

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))

 



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Hi Parry2k,

 

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

 

Thanks

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



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

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.



Subscribe to the @PowerBIHowTo YT channel for an upcoming video on List and Record functions in Power Query!!

Learn Power BI and Fabric - subscribe to our YT channel - Click here: @PowerBIHowTo

If my solution proved useful, I'd be delighted to receive Kudos. When you put effort into asking a question, it's equally thoughtful to acknowledge and give Kudos to the individual who helped you solve the problem. It's a small gesture that shows appreciation and encouragement! ❤


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.

Sean
Community Champion
Community Champion

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

SqlJason
Memorable Member
Memorable Member

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

Hi ,

 

Actually my data is like this.

 

01-jan-15           1.5

01-feb-15           1.0

01-mar-15          2.0

01-jan-16            1.0

01-feb-16            0

01-mar-16            0

01-jan-17           1.0

01-feb-17           2.0

01-mar-17           0

 

My output data should be like:

 

01-mar-15     2.0

01-mar-16     1.0 ( since mar16 and feb16 value =0 , i considered jan16 value)

01-mar-17      2.0 ( since mar17 value=0 , i considere prior month value i.e., 2.0)

 

hope it is clear.

 

Thanks for your valuable inputs so far.

 

Thanks

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

Hi sqlJason,

 

i was able to complete the requirement using DAX functon you suggested.  i want to enhance the dax function.In the variable section filters field i want to pass parameter based on user selection. 

 

Eg: there is a slicer in the report : 

 

REGION :   East West North South

Based on this selection(say if the user clicked on WEST , i want to dynamically change the VARIABLE as :

 

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

 is this possible.

 

Thanks

 

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.  

Got it... Applied your solution and its working perfect.

 

Thank you Very much

Sean
Community Champion
Community Champion

@revansh

Okay using your sample data I created a Table called 'Table'

with columns Date and Value then...

1) Create a Year COLUMN

Year = YEAR('Table'[Date])

2) Sales MEASURE

Sales = SUM('Table'[Value])

3) Another MEASURE

Last Date (Sales > 0) = 
CALCULATE (
    LASTDATE ( 'Table'[Date] ),
    VALUES ( 'Table'[Year] ), 
    FILTER ( 'Table', [Sales] > 0 ) 
)

4) and Final MEASURE

Sales on Last Date = 
CALCULATE (
    [Sales],
    FILTER (
        'Table',
        'Table'[Date]
            = CALCULATE (
                LASTDATE ( 'Table'[Date] ),
                VALUES ( 'Table'[Year] ),
                FILTER ( 'Table', [Sales] > 0 )
            )
    )
)

Obviously you can rename all of these...

Here's the result - all sample data on the left and desired outcome in the table on the right

which is a Table Visual with the Year Column and the Last Date (Sales > 0) and KPI on Last Date Measures

DAX Functions.png

 

Hope this helps! Smiley Happy

Sean
Community Champion
Community Champion

Smiley LOL @SqlJason you could not resist typing this on your phone Smiley Very Happy

 

but yes it is easier indeed Smiley Very Happy for some reason the font is smaller when you respond from a phone!

Helpful resources

Announcements
April AMA free

Microsoft Fabric AMA Livestream

Join us Tuesday, April 09, 9:00 – 10:00 AM PST for a live, expert-led Q&A session on all things Microsoft Fabric!

March Fabric Community Update

Fabric Community Update - March 2024

Find out what's new and trending in the Fabric Community.