.NET GENES

Good noon! I was recently confronted with an issue on dates not sorted correctly of which a date with this value 2020/10 (October 2020) appears in between of 2020/2 and 2020/1. This was based from an article I read on how to change the current date into a Current Month string and the data type for this calculated column is Text . As you can see from the image below, month of November which is the current month is translated as " Current Month ". But October 2020 is in between January and February 2020 respectively. And here's the calculated column DAX script. Current Month = IF ( MONTH ( 'SalesOrder' [OrderDate] ) = MONTH ( NOW () ) && YEAR ( 'SalesOrder' [OrderDate] ) = YEAR ( NOW () ), "Current Month" , YEAR ( SalesOrder[OrderDate] ) & "/" & MONTH ( SalesOrder[OrderDate] ) ) Since my datasource came from SQL Server database and the storage mode is DirectQuery , I can't just

Guys, Here's a DAX formula that will perform union of two tables given their specific column names in Power BI. The trick is to use SELECTCOLUMNS () function for each table. Countries = UNION ( SELECTCOLUMNS ( 'Main Location' , "Countries" , 'Main Location' [Countries] ), SELECTCOLUMNS ( 'Sales' , "Countries" , 'Sales' [Countries] ) )

Gents, Today, I had an issue with month values in my line chart visual not sorting correctly. The month values were plainly text in type and it seems the line chart does not recognize it's order. In order for these to work, I need to create a month column to explicitly include the year and retrieve the value using DateValue() function. If the measure is added in the visual, set the proper date format in Power BI desktop for the line chart to recognize the proper sequence. MonthYear = var monthName = TRIM (GrossProfitTrend[MonthName]) Return SWITCH(monthName, "Jan" , DATEVALUE( MONTH ( DATE ( YEAR (NOW()), 1 , 1 )) & " " & YEAR (NOW())) , "Feb" , DATEVALUE( MONTH ( DATE ( YEAR (NOW()), 2 , 1 )) & " " & YEAR (NOW())) , "Mar" , DATEVALUE( MONTH ( DATE ( YEAR (NOW()), 3 , 1 )) & " " & YEAR (NOW())) , "Apr" , DATEVALUE( MONTH ( DATE ( YEAR (NOW()), 4 , 1 )

Good evening fellow developers, I have a scenario wherein I will set the default value of a slicer to the current month by default. A link in the forums presented the solution below. Current Month = IF( MONTH ( 'Transactions' [transdate]) = Month (NOW()) && YEAR ( 'Transactions' [transdate]) = YEAR (NOW()), "Current Month" , Format( 'Transactions' [transdate], "YYYY mmmm" )) Since, I'm using DirectQuery connection, the Format() function isn't allowed in calculated columns. After doing some tweaking on my column, the fix that I've come up with was to extract the month and year individually and concatenate them and return that value to the Current Month column. Current Month = IF( MONTH ( 'Transactions' [transdate]) = Month (NOW()) && YEAR ( 'Transactions' [transdate]) = YEAR (NOW()), "Current Month" , Year ( 'Transactions' [transdate]) & "/" & Month

Hello Team, I've started learning grasping the concepts of DAX through videos and blog posts using DAX Studio since most of the tasks I'm currently working are Power BI reports. I also want to experiment on how can I simplify or modify the formula using other functions so as to make it easier to understand. One example is a nested Filter() formula below. EVALUATE FILTER ( FILTER ( Products, Products[UnitPrice] > 50 ), Products[UnitsInStock] > 10 ) The first parameter of the outer FILTER() function is another filter which returns a table Products with UnitPrice of greater than 50. Then the second parameter filters the table returned by removing Products whose UnitInStock is less than or equal to 10. Upon recognizing the formula, I can simplify that to use AND() function and pass those two filter conditions in the function. The simplified formula, returns same results. EVALUATE FILTER ( Products, AND ( Products[UnitPrice] > 50 , Products[UnitsInStock]

Here's a simple DAX formula that I learned from the forums on how to show the negative numbers into the Matrix Visual without negative sign but enclosed in parenthesis. Month Sales Formatted = FORMAT([ Month Sales Measure], "#,##0;(#,##0)" ) Cheers!

I have this DAX formula that counts the number of rows using Calculate() function with [RowCount] as the expression and a certain condition as the filter. CountARCurrent = CALCULATE([RowCount], AccountsReceivablesAging[ Current ] > 0 ) This works on most of the report's dataset however when I tried importing a new dataset, the formula above didn't work. The data used in the filter is a floating point number and the datasets involved in this project are imported via Direct Query. After searching the Power BI forums, I found an alternative solution which is to use COUNTROWS() function instead of [RowCount] as the expression. CountARCurrent = CALCULATE(COUNTROWS(AccountsReceivablesAging), AccountsReceivablesAging[ Current ] > 0 )

Hi, Just sharing a simple DAX formula to round off floating point numbers to int. AvgDayToPayRnd = Int (ROUND(AccountsReceivablesAging[AvgDayToPay], 0 )) First, you have to use the Round() function passing in the table column as the first parameter and 0 which means no trailing zeroes in the second parameter. Then cast that number to an integer type using the Int() function. Cheers!