Excelent Ruth! Bravo. I was looking a solution for something and no I found it today in this video. You make DAX to seam easy and now I am starting to see that it is actually easy... not dificult...just diferent. Thank you one more time!
Hello ruth.. very nice video! Is it possible to write in dax the periode between friday 06:00 A.M and monday 06:00 A.M? For my job i report every monday the data of te weekend between that timeframe. I would like to make a button to select the weekend without using the filter option. Happy holidays 🌲
Many thanks for the video. I thought i was going crazy why it wasn't working until you pointed out about decimal points and the need to put a space after the comma! Also your advice on testing your DAX is important, which helped to solve my other issue.
Hi Ruth, Is there any way to pick date picker value using selected value, every time I used it return nothing with date column in slicer. Pleased suggest if I can pick date slicer value. Thanks
Hi, I don’t think you can do it with selectvalue as a date picker returns two dates and selectvalue can only return one. Post the question in the power bi community to get more detailed help! /Ruth
Hi Ruth, yet another great video. Questions: If I have three columns: Start Date, End Date, Day Rate. Can I calculate the sum of the "Day Rate" for a period, for multiple rows, where the selected period overlaps parts of some rows?
I am wondering , why we cannot use the order date from the fact table instead of the dimdate-table. I tried and turned out completely messed up. So what is the problem?
Hi Ruth, I have a difficult case that I cant display the total sale for the same period (date/week/month/year) in the BI For exp If I choose a date at the filter box, the visual will display the total sales for the same date of last moth, last 2 month/3 months..., display the total sales for the same period of week (the accumulated sales up to Mon/Tue/Wed... of the last 1/2/3/4 weeks)... Actually, I have to prepare reports on the excel workbook. So can you help to resolve this? Many thanks!
Tengo 2 tablas, una mi tabla calendario y otra mi tabla de eventos donde una persona checa su entrada, el problema esta cuando la persona no checa, por logica no tenemos evento no hay registro como le puedo decir o como hacerle para que comparándola con mi tabla calendario identifique ese evento (fecha) que no existe, no hay registro, espero y me puedan ayudar gracias!!
Hi Ruth and thank you for another great video, i have a question. Can we use DATESBETWEEN with COUNTROWS for that table so we can calculate for example the days of the first purchase of a customer and the final (or last day) of that same customer purchase?
Below is the measure, Rolling profit (5 day) = CALCULATE( [Total Profit], DATESBETWEEN( 'Calendar Table'[Date], DATEADD( LASTDATE('Calendar Table'[Date]), -5, DAY ) , LASTDATE('Calendar Table'[Date]) ) ) Why are we using lastdate() function within the dateadd() function. Although it is not working without the lastdate() function, but I think it should work. Let's say we have 20-02-2020 in the current row in a table visual. Then how will this measure evaluate and what is the use of lastdate(). I am stuck on this since so many days. Please help :)
Hey ruth! you are one of the best resources on the entire internet for powerBI, you have helped me so much its unbelievable! I have a question though - how could i write this to preserve the row context? IE: What if i wanted to put the calculate([measure],datesbetween()) measure in a table with the date column from my date table as the rows? When i try this, i get the same value for every date, i figure its because using calculate() ignores the row context. any work around for this?
Hi! How would you find the business dates between a given date and today? Is that allowed in this function? and Can you use this DATESBETWEEN function multiple times? For example : IF (AnotherTable[EndUserFormProvidedDate2="", CALCULATE(SUM(DateTable[WORKDAY]), DATESBETWEEEN(DateTable[DATE], AnotherTable[EndUserFormProvidedDate1], TODAY(), ELSE CALCULATE(SUM(DateTable[WORKDAY]), DATESBETWEEEN(DateTable[DATE], AnotherTable[EndUserFormProvidedDate1], AnotherTable[EndUserFormProvidedDate2], Thanks, Na
Hi, I've tried the PowerBI Community and It doesn't seem anyone can help. I also sought a tutor and one isn't available. Here's the link: community.powerbi.com/t5/Desktop/DAX-Nested-IF/m-p/254063#M113054
Hi Ruth, love the video, and many congratulations on the MVP re-award :) I have a challenge similar to this scenario, but I am looking to dynamically calculate DATEDIFF between two milestones when selected by a user - do you by any chance have a video on your channel which may cover this topic? Appreciate the details are limited, and my next step would be the Power BI forums :)
No video on that I am afraid, but when you check the power bi community make sure you detail what you are trying to do and more importantly how your model looks like to make sure they help you right away :) /Ruth
finally I understood!! Now another challeng how can I deal with hours minute and second? I mean becouse on the colum "date " I have a date in the following format "dd/mm/yyyy hh:mm:ss" and becouse on the same day I have more than a value the function datebetween return me an error becouse the colum date contains multiples value... how can I fix it?
Ruth, you are like a business colleague who everyone would want by his side. Friendly, fresh, bright.
Hahaha , Thanks! Best compliment ever ;)
/Ruth
I am a business user, non IT background and I have learned a lot from your videos, the way you explain things , anyone can learn Power BI :)
Excelent Ruth! Bravo. I was looking a solution for something and no I found it today in this video. You make DAX to seam easy and now I am starting to see that it is actually easy... not dificult...just diferent. Thank you one more time!
Oh! Wonderful and congrats!
You just Leveled Up 😉!
/Ruth
Yeah! this is the feeling!
Hello ruth.. very nice video!
Is it possible to write in dax the periode between friday 06:00 A.M and monday 06:00 A.M?
For my job i report every monday the data of te weekend between that timeframe. I would like to make a button to select the weekend without using the filter option.
Happy holidays 🌲
Thanks Ruth.... Great video again....
Glad you liked it :)
/Ruth
Many thanks for the video. I thought i was going crazy why it wasn't working until you pointed out about decimal points and the need to put a space after the comma! Also your advice on testing your DAX is important, which helped to solve my other issue.
Wonderful and thanks for sharing ;)
/Ruth
Like the way you explain things....another great video
Thanks Vijay!
Gracias Ruth, con este gran ejemplo me ayudaste a resolver un calculo de un reporte que no encontraba como hacerlo!!!👍
Genial!!
Ruth you are awesome, we love you.
Sending some ❤️ back !
/Ruth
Hi Ruth, Is there any way to pick date picker value using selected value, every time I used it return nothing with date column in slicer. Pleased suggest if I can pick date slicer value. Thanks
Hi,
I don’t think you can do it with selectvalue as a date picker returns two dates and selectvalue can only return one.
Post the question in the power bi community to get more detailed help!
/Ruth
Thanks Ruth.
Hi Ruth, yet another great video. Questions: If I have three columns: Start Date, End Date, Day Rate. Can I calculate the sum of the "Day Rate" for a period, for multiple rows, where the selected period overlaps parts of some rows?
I screamed, thank you. Whoo hoo. I have had sleepless nights
🥳🥳
what is the name of visualization you used on adjusting the timeline ? Thank you!
It is a slicer on a date column.
/Ruth
I am wondering , why we cannot use the order date from the fact table instead of the dimdate-table. I tried and turned out completely messed up. So what is the problem?
Check this video:
m.th-cam.com/video/q9WZTNqjlKI/w-d-xo.html
/Ruth
Hi Ruth, Is there a way to get first day of the current month dynamically?
Try EOMONTH
/Ruth
Thank you for sharing your knowledge!
Hi Ruth,
I have a difficult case that I cant display the total sale for the same period (date/week/month/year) in the BI
For exp
If I choose a date at the filter box, the visual will display the total sales for the same date of last moth, last 2 month/3 months..., display the total sales for the same period of week (the accumulated sales up to Mon/Tue/Wed... of the last 1/2/3/4 weeks)...
Actually, I have to prepare reports on the excel workbook.
So can you help to resolve this?
Many thanks!
Hi Minh,
Could you please post your question in the Power bi Community? Thanks
/Ruth
Tengo 2 tablas, una mi tabla calendario y otra mi tabla de eventos donde una persona checa su entrada, el problema esta cuando la persona no checa, por logica no tenemos evento no hay registro como le puedo decir o como hacerle para que comparándola con mi tabla calendario identifique ese evento (fecha) que no existe, no hay registro, espero y me puedan ayudar gracias!!
Can you please make video of measure used as snapshot ... over period of time for inventory example
Hi! Mmmm not sure what you mean? Can you explain more?
/Ruth
how do you compare 2 dates??? count (# of loans ) where date 1
HI RUTH, in my case I don't have a custom calendar I have the start and end date for which i want to create lists of months inside a column
Hi Deepak,
Always use a custom calendar 📅
/Ruth
Hi Ruth! Do you know how to filter a table by dates (row by row) using DAX? The output would be a second table. Thank you!
Many ways to do that, please provide more details in the power bi community to get personalized help!
/Ruth
Hi Ruth and thank you for another great video, i have a question. Can we use DATESBETWEEN with COUNTROWS for that table so we can calculate for example the days of the first purchase of a customer and the final (or last day) of that same customer purchase?
Search for customer churn (should do a video at some point) to get optimized versions of those calculations.
Happy Friday!
/Ruth
Much appreciated , would you please hep me how i can calculate month on cover , number of months until deplete the stock
Hi Ahmad,
That question is perfect for the power Bi community. Please post as much details as possible to get help.
Community.powerbi.com
/Ruth
Below is the measure,
Rolling profit (5 day) =
CALCULATE(
[Total Profit],
DATESBETWEEN(
'Calendar Table'[Date],
DATEADD(
LASTDATE('Calendar Table'[Date]),
-5,
DAY
) ,
LASTDATE('Calendar Table'[Date])
)
)
Why are we using lastdate() function within the dateadd() function. Although it is not working without the lastdate() function, but I think it should work. Let's say we have 20-02-2020 in the current row in a table visual. Then how will this measure evaluate and what is the use of lastdate().
I am stuck on this since so many days. Please help :)
Hey ruth! you are one of the best resources on the entire internet for powerBI, you have helped me so much its unbelievable! I have a question though - how could i write this to preserve the row context? IE: What if i wanted to put the calculate([measure],datesbetween()) measure in a table with the date column from my date table as the rows? When i try this, i get the same value for every date, i figure its because using calculate() ignores the row context. any work around for this?
Hi! How would you find the business dates between a given date and today? Is that allowed in this function? and Can you use this DATESBETWEEN function multiple times?
For example :
IF (AnotherTable[EndUserFormProvidedDate2="",
CALCULATE(SUM(DateTable[WORKDAY]), DATESBETWEEEN(DateTable[DATE], AnotherTable[EndUserFormProvidedDate1], TODAY(),
ELSE
CALCULATE(SUM(DateTable[WORKDAY]), DATESBETWEEEN(DateTable[DATE], AnotherTable[EndUserFormProvidedDate1], AnotherTable[EndUserFormProvidedDate2],
Thanks,
Na
Yes and yes!
Post your case in the power bi community with some sample data for more detailed help :)
/Ruth
Hi, I've tried the PowerBI Community and It doesn't seem anyone can help. I also sought a tutor and one isn't available. Here's the link: community.powerbi.com/t5/Desktop/DAX-Nested-IF/m-p/254063#M113054
Good Day.
Could this also be done in excel power query?
Havent tried, give it a ho and let us know !
/Ruth
lol was that issue fixed with the function thinking its a decimal?
glad your name showed at the start. I could never work out what it is and I've watched many of your vids :)
Ouch, I should add it again on my vids!
Hi Ruth, love the video, and many congratulations on the MVP re-award :) I have a challenge similar to this scenario, but I am looking to dynamically calculate DATEDIFF between two milestones when selected by a user - do you by any chance have a video on your channel which may cover this topic? Appreciate the details are limited, and my next step would be the Power BI forums :)
No video on that I am afraid, but when you check the power bi community make sure you detail what you are trying to do and more importantly how your model looks like to make sure they help you right away :)
/Ruth
Will be sure to - thanks Ruth!! :)
finally I understood!! Now another challeng how can I deal with hours minute and second? I mean becouse on the colum "date " I have a date in the following format "dd/mm/yyyy hh:mm:ss" and becouse on the same day I have more than a value the function datebetween return me an error becouse the colum date contains multiples value... how can I fix it?
Thanks! Works here!
Thanks
You probably have comma as marker for decimal and not period in the setting. thats the reason may be you have the problem in dax expression.
¡So cool!
It is!!