Forum Discussion
Power BI DAX
Hi, I have two columns Date and Total Sales. Now I need to split the Total Sales column into 3 columns.
First is based on selected month and year I need total sales upto previous Financial year ending.
Second is based on selected month and year I need total sales from that Financial year starting to previous month.
Third is total sales for the selected month and year.
6 Replies
Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
https://community.powerbi.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
https://community.powerbi.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523Hi,
You have not specified the FY so i have assumed that it is Jan - Dec. Try this process
- Create a Calendar Table with calculated column formulas for Year, Month name and Month number. Sort the Month name column by the Month number
- Create a relationship (Many to One and Single) from the Date column of the Data Table to the Date column of the Calendar table
- To your slicer, drag Year and Month name from the Calendar Table and select any oen Year and Month name
- Write these measures
Total sales = sum(Data[Sales])
Total sales in previous FY = calculate([Total sales],previousyear(calendar[date]))
Total sales in present FY = calculate([Total sales],datesbetween(calendar[date],date(year(min(calendar[date]),1,1)),min(calendar[date])-1))
Hope this helps.
koiralasmn please create a new post. There is a difference between calculated columns and measures. Measures require some sort of aggregation like SUM()
I encountered an issue with calculating the ratio in RLS. When RLS is enabled, the ratio calculation does not work correctly. To work around this problem, I created a separate table to calculate the sales ratio and linked it to a date table. However, the result is not correct. The amount 'Prélevement'[Facial] calculation is correct, but the ratio is incorrect. I tried multiplying the result by 100 to obtain the ratio as a percentage, but without success. Here is the measure used:
Could you help me identify where the problem lies and how to correct the ratio calculation?Ratio = SUMMARIZE( 'Prélevement', 'Prélevement'[Date Opération], 'Prélevement'[Cd Dist], 'Prélevement'[Distrib], 'Prélevement'[Facial], "Total Ventes", SUM('Prélevement'[Facial]), "Total Qte", SUM('Prélevement'[Qte Livree]), "Ratio Vente", DIVIDE(SUM('Prélevement'[Facial]),CALCULATE(SUM('Prélevement'[Facial]),ALL('Prélevement')))*100)RedaBi Please create a new thread. Please provide sample data that covers your issue or question completely, in a usable format (not as a screenshot).
Do not include sensitive information or anything not related to the issue or question.
If you are unsure how to upload data please refer to https://community.fabric.microsoft.com/t5/Community-Blog/How-to-provide-sample-data-in-the-Power-BI-Forum/ba-p/963216
Please show the expected outcome based on the sample data you provided.
Want faster answers? https://community.fabric.microsoft.com/t5/Desktop/How-to-Get-Your-Question-Answered-Quickly/m-p/1447523
