I am very new to Power BI and I am having a hard time converting a sql statement to dax. I work in law enforcement and need to count the number of cases we have had during a certain date range in a 10 year period. For example January 1 - June 15 for the years 2010 - 2019. Following is my current sql statement:WHERE (((Year([DATEOFDEAT]))>=2010) AND ((Month([DATEOFDEAT])) Between 1 And 5) AND ((Day([DATEOFDEAT]))<Date())) OR (((Year([DATEOFDEAT]))>=2010) AND ((Month([DATEOFDEAT]))=6) AND ((Day([DATEOFDEAT])) Between 1 And 15)) As a workaround, I created one dax statement for each year. So, ten total dax statements for one report tile which feels like overkill. Here is an example of the dax for year 2010:1 Count of Murders 2010 = 2 CALCULATE(3 COUNT(tblMurders[CN]),4 DATESBETWEEN(5 tblMurders[DATEOFDEAT].[Date],6 DATE(2010,1,1),7 DATE(2010,6,15)8 )9 ) Any help would be very much appreciated. Thanks!!!

Anonymous If you want to use my idea of a MonthDay, you can create this calculated column in your datetable: (replace 'Table 2'[Date] references to your table name and date column):MonthDay = VALUE(CONCATENATE(FORMAT(MONTH('Table 2'[Date]), "#"), FORMAT(DAY('Table 2'[Date]), "0#")))Outcome: Kind regardsDjerro123-------------------------------If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.Keep those thumbs up coming! 🙂

Anonymous

Not applicable

6 years ago

Solved

How to convert a sql statement to dax

I am very new to Power BI and I am having a hard time converting a sql statement to dax. I work in law enforcement and need to count the number of cases we have had during a certain date range in a 10 year period. For example January 1 - June 15 for the years 2010 - 2019. Following is my current sql statement:

WHERE (((Year([DATEOFDEAT]))>=2010) AND ((Month([DATEOFDEAT])) Between 1 And 5) AND ((Day([DATEOFDEAT]))<Date())) OR (((Year([DATEOFDEAT]))>=2010) AND ((Month([DATEOFDEAT]))=6) AND ((Day([DATEOFDEAT])) Between 1 And 15))

As a workaround, I created one dax statement for each year. So, ten total dax statements for one report tile which feels like overkill. Here is an example of the dax for year 2010:

1 Count of Murders 2010 =

2 CALCULATE(

3 COUNT(tblMurders[CN]),

4 DATESBETWEEN(

5 tblMurders[DATEOFDEAT].[Date],

6 DATE(2010,1,1),

7 DATE(2010,6,15)

8 )

9 )

Any help would be very much appreciated.

Thanks!!!

Need Help

JarroVGIT
6 years ago
Anonymous

If you want to use my idea of a MonthDay, you can create this calculated column in your datetable: (replace 'Table 2'[Date] references to your table name and date column):
MonthDay = VALUE(CONCATENATE(FORMAT(MONTH('Table 2'[Date]), "#"), FORMAT(DAY('Table 2'[Date]), "0#")))
Outcome:

Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂

16 Replies

JarroVGIT
Resident Rockstar
6 years ago
Hi Anonymous,
The table you will create is dynamic and rebuild every refresh. This is a best practice when dealing with date columns in your data. Actually, PowerBI is creating a hidden datetable when your data contains dates. Creating your own makes sure you can do some time intelligence.
See this link: https://docs.microsoft.com/en-us/power-bi/desktop-date-tables
I would urge you to reconsider your position, I can fullheartedly recommend using a datetable 🙂
- Anonymous
  Not applicable
  6 years ago
  JarroVGIT
  
  Btw, the article you linked to was super helpful! I found a youtube video on how to easily create a date table using DAX. With your help and the youtube video, I created a date table with the following dax.
  
  1 Calendar = ADDCOLUMNS(
  2 CALENDAR("1-1-2007","12-31-2050"),
  3 "DateAsInteger",FORMAT([Date],"YYYYMMDD"),
  4 "MonthNumber",FORMAT([Date],"MM"),
  5 "DayNumber",FORMAT([Date],"DD"),
  6 "Year",YEAR([Date]),
  7 "MonthDayNumber",FORMAT([Date], "MMDD"),
  8 "MonthYearNumber",FORMAT([Date],"MM/YYYY"),
  9 "MonthYearShort",FORMAT([Date],"mmm/YYYY"),
  10 "MonthNameShort",FORMAT([Date],"mmm"),
  11 "DOWNumber",FORMAT([Date],WEEKDAY([Date])),
  12 "DOWNameLong",FORMAT([Date],"dddd"),
  13 "DOWNameShort",FORMAT([Date],"ddd"),
  14 "Quarter","Q" & FORMAT([Date],"Q"),
  15 "YearQuarter",FORMAT([Date],"YYYY") & "/Q" & FORMAT([Date],"Q"))
  
  So, thanks again!! This community is great! dax
  - JarroVGIT
    Resident Rockstar
    6 years ago
    No Problem, glad your issue is resolved 🙂
    
    Keep those thumbs up coming! 🙂
JarroVGIT
Resident Rockstar
6 years ago
Hi Anonymous ,
I would recommend creating a Date table with a MonthDay (e.g. 0131 for Jan 31st) and Yearnumber column. Then create a relationship between Datetable[Date] and tblMurders[DATEOFDEAT]. Then you can create a measure like this:

Measure = CALCULATE( COUNT (tblMurders[CN]), FILTER(ALL(tblMurders), RELATED(Datetable[Year]) > 2010 && RELATED(Datetable[Year]) < 2019 && RELATED(Datetable[MonthDay]) > 0101 && RELATED(Datetable[MonthDay]) < 0615))

Let me know if that works! 🙂
PS. this is a great case for using 6 'what-if parameters', which you can incorporate into your measure. A from_day, from_month, from_year and to_day, to_month, to_year parameter, which you can include in your measure to make your report dynamic 🙂

Kind regards
Djerro123
-------------------------------
If this answered your question, please mark it as the Solution. This also helps others to find what they are looking for.
Keep those thumbs up coming! 🙂
- Anonymous
  Not applicable
  6 years ago
  JarroVGIT
  
  This report is going to be updated on a weekly basis. So, I am not sure creating a separate table would be conducive.
  
  Thank you so much for your reply.
dax
Community Support
6 years ago
Hi Kerri3997,

You could refer to my sample for details.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.

1218.pbix99 KB
mwegener
Most Valuable Professional
6 years ago
Hi Anonymous ,

why don't you filter through a visual?
If I answered your question, please mark my post as solution, this will also help others.
Please give Kudos for support.
- Anonymous
  Not applicable
  6 years ago
  Hi mwegener ,
  
  Filtering this way picks up all dates (January 1 - December 31) for years 2010 - 2018. I only need January 1 - June 15 for all 10 years.
  
  Thank you though.
  - Anonymous
    Not applicable
    6 years ago
    Hi Anonymous
    create one column in table which is month year of date and create below measure :
    CALCULATE(COUNT(financials[ Sales]),FILTER(financials,financials[Date]<=DATE(2019,6,15) && financials[Date]>=DATE(2010,1,1) && financials[MonthDay]<=0615))
dax
Community Support
6 years ago
Hi Kerri3997,

Could you please tell us if your question has been resolved. If so, in order to close the thread, please kindly mark helpful replies as answers. By doing so, it will benefit all community members who are having this similar issue. Your contribution is highly appreciated.

Best Regards,
Zoe Zhi

If this post helps, then please consider Accept it as the solution to help the other members find it more quickly.
- Anonymous
  Not applicable
  6 years ago
  dax
  
  Sorry, I am just now getting time to test the solutions posted. As soon as I am able to confirm a solution, I will mark it.
  
  Thanks so much!

Forum Discussion

How to convert a sql statement to dax

16 Replies

Related content

Converting Epoch Date fields within the import statement

Convert ISNULL case statement to DAX

OR Statement ?

Converting PostGreSQL Date time field at the import using SQL Statement

SQL Statement opcion error