Why Time Intelligence Fails
Power BI provides a very strong time intelligence, with a great list of functions around. But still, Time Intelligence fails. This is because the setup is not correct.
Five key points to remember
Use Date Table (Join with Fact Date)
Date table is marked as a date table
Use columns from date table
Date does not have a timestamp
Date table has all dates
I have discussed these in details in my Video at YouTube
Please let me know your comments.
This link will take you to the power bi dashboard that has links for…
How to create report in power bi service
In December 2020, Power BI release a new feature that will allow users to create report on power BI service. You can Enter New data or use existing datasets.
Release Note: https://powerbi.microsoft.com/en-us/blog/quickly-create-reports-power-bi-service/
I have explored this feature in a Video
Please let us know your comments on this feature.
This link will take you to the power bi dashboard that has links for all my blogs and videos — You can get all my posts at https://community.powerbi.com/t5/Data-Stories-Gallery/Blog-Analysis/m-p/1265567#M4403 Like, Share, comment, Give suggestions for improvement, and next post/video.
You Can watch my…
Power BI Download link: latest version
https://powerbi.microsoft.com/en-us/downloads/
Power BI Download link: Old versions
https://docs.microsoft.com/en-us/power-bi/fundamentals/desktop-latest-update-archive
Latest update from Power BI including monthly Release Announcement
https://powerbi.microsoft.com/en-us/blog/
https://powerbi.microsoft.com/en-us/blog/category/uncategorized/
Power BI Guidance document from Microsoft
https://docs.microsoft.com/en-us/power-bi/guidance/
DAX Functions documentation
https://docs.microsoft.com/en-us/dax/
Power BI Community Support forum Link
First Refer, How to Get Your Question Answered Quickly
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1448253#M607324
Community: https://community.powerbi.com/
Desktop: https://community.powerbi.com/t5/Desktop/bd-p/power-bi-designer
Service: https://community.powerbi.com/t5/Service/bd-p/power-bi-web-app
Report Server: https://community.powerbi.com/t5/Report-Server/bd-p/ReportServer
Power Query: https://community.powerbi.com/t5/Power-Query/bd-p/power-bi-services
DAX: https://community.powerbi.com/t5/DAX-Commands-and-Tips/bd-p/DAXCommands
Mobile: https://community.powerbi.com/t5/Mobile-Apps/bd-p/power-bi-mobile
Developer: https://community.powerbi.com/t5/Developer/bd-p/Developer
Power BI Gallery:
Theme: https://community.powerbi.com/t5/Themes-Gallery/bd-p/ThemesGallery
Data Stories: https://community.powerbi.com/t5/Data-Stories-Gallery/bd-p/DataStoriesGallery
Webinars and Video Gallery: https://community.powerbi.com/t5/Webinars-and-Video-Gallery/bd-p/VideoTipsTricks
MBAS Gallery: https://community.powerbi.com/t5/MBAS-Gallery/bd-p/MBAS_Gallery
Amazing Power BI Blog by Community Users
https://community.powerbi.com/t5/Community-Blog/ct-p/PBI_Comm_CommunityBlog
New feature/s idea/suggestion to Power BI
https://ideas.powerbi.com/ideas/
Power BI Issue Reporting
https://community.powerbi.com/t5/Issues/idb-p/Issues
Power BI Support
https://powerbi.microsoft.com/en-us/support/
Custom Visual
https://appsource.microsoft.com/en-us/marketplace/apps?product=power-bi-visuals
Important Youtube Channels
Guy in a Cube: https://www.youtube.com/channel/UCFp1vaKzpfvoGai0vE5VJ0w
Curbal: https://www.youtube.com/c/CurbalEN
SQLBI: https://www.youtube.com/user/sqlbit
RADCAD: https://www.youtube.com/channel/UCsOfIwAXj1fT6LDqEDEAb4g
Havens Consulting: https://www.youtube.com/channel/UCjlfQwqb-0S40XQ8seYPLSw
Ofcourse my Channel : https://www.youtube.com/c/Amitchandak
In this blog, we will try to answer Top 10 Frequently Asked Questions on Day Level Intelligence in Power BI.
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
- https://community.powerbi.com/t5/Community-Blog/Creating-Financial-Calendar-Decoding-Date-and-Calendar-1-5-Power/ba-p/1187441
- https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.).
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Is Today = if('Date'[Date]=TODAY(),"Today",[Date]&"")- What are functions for Day Level Intelligence?
PREVIOUSDAY, NEXTDAY
- How can we get previous day data?
Previous Day Sales = CALCULATE([Sales],PREVIOUSDAY('Date'[Date]))
Day behind Sales = CALCULATE([Sales],dateadd('Date'[Date],-1,Day))
Last Day = CALCULATE([Sales], FILTER(ALL('Date'),'Date'[Date]=max('Date'[Date])-1))- If the dates are not continuous then how…
In this blog, we will try to answer, 10 Most Frequently Asked Questions on Week Till Date(WTD) in Power BI.
Abbreviations:
WTD : Week Till Date
LWTD : Last Week Till Date
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Week Start date = 'Date'[Date] + -1*WEEKDAY('Date'[Date],2)+1
Week End date = 'Date'[Date] + 7 –1*WEEKDAY('Date'[Date],2)
Week Number = WEEKNUM([Date],2)
Week = if('Date'[Week Number]<10,'Date'[Year]*10 & 'Date'[Week Number],'Date'[Year]&'Date'[Week Number])
Week Rank = RANKX(all('Date'),'Date'[Week Start date],,ASC,Dense)
Week name = [Week Start date]…In this blog, we will try to answer, 10 Most Frequently Asked Questions on Month Till Date (MTD) in Power BI.
Abbreviations:
MTD: Month Till Date
MQTD: Last Month Till Date
LMQTD : Last Year Same Month Till Date
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Start Of Year = STARTOFYEAR(‘Date’[Date],”12/31")
End of Year = ENDOFYEAR(‘Date’[Date],”12/31")
Day of Year = Datediff([Start Of Year],[Date],Day)+1
Year = YEAR(‘Date’[Date])Start Date of Month = STARTOFMONTH('Date'[Date])
End Date…
In this blog, we will try to answer, 10 Most Frequently Asked Questions on Quarter Till Date (QTD) in Power BI.
Abbreviations:
QTD: Quarter Till Date
LQTD: Last Quarter Till Date
LYQTD : Last Year Same Quarter Till Date
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Start Of Year = STARTOFYEAR(‘Date’[Date],”12/31")
End of Year = ENDOFYEAR(‘Date’[Date],”12/31")
Day of Year = Datediff([Start Of Year],[Date],Day)+1
Year = YEAR(‘Date’[Date])Start Of Qtr = STARTOFQUARTER(‘Date’[Date])
End Of Qtr…
In this blog, we will try to answer, 10 Most Frequently Asked Questions on Year Till Date (YTD) in Power BI.
Abbreviations:
YTD: Year Till Date
LYTD: Last Year Till Date
Pre-requisites:
For any Time Intelligence, you must have a date table. Please refer the below links to create one date table.
https://radacad.com/creating-calendar-table-in-power-bi-using-dax-functions
Make sure your calendar has the following fields.(For a financial year, which is not same as the calendar year, refer the Power BI community blog for some of these calculations.)
Date = CALENDAR(date(2015,03,01),Date(2021,02,28)) //calendar
Start Of Year = STARTOFYEAR(‘Date’[Date],”12/31")
End of Year = ENDOFYEAR(‘Date’[Date],”12/31")
Day of Year = Datediff([Start Of Year],[Date],Day)+1
Year = YEAR(‘Date’[Date])- What are the various functions for YTD in Power BI?
DATESYTD and TOTALYTD
YTD = calculate([Sales],datesytd(‘Date’[Date]))
YTD…When you are using a star schema for your warehouse or for reporting system and you faces data mismatch issues , then how to debug it; how to find the root cause for the mismatch. Is data mismatch is only at query level or correct data in not there in data warehouse or not collected from source.
We will consider the following warehouse star schema to understand the problem
1. Fact_sales (row_id, Sales_date, Creation_date, Delivery_date, Sales_value,Del_value, Item_id, region_id , customer_id,order_no, emp_id)
2. Dim_Customer (Customer_id , Customer_code, Customer_name)
3. Dim_Item (item_id, item_code , Item_name, Item_cat_desc)
4. Dim_region (region_id, region_code, city, state…
Data and its analysis is the need of the hour. Business Intelligence and Analytics tools are enablers for data driven culture. Often comparing data between two time slices is commonly sought after. Say, for example, one would like to compare sales of this year vs last year. Or for a custom period like sales of this year Christmas season vs last year Christmas season. You may want to measure the sales performance of those two periods and find the growth or de-growth.
Power BI, one of the leading BI visualization tool, offers you various ways to do it. …
Amit Chandak
Amit has 18+ years of experience in Business Intelligence, Analytics, Data Science. He is Chief Analytics Officer at Kanerika & Power BI community Super User
