Showing End of Month value ignoring date slicer

[hieudm6]

Hi, I am having 02 datasets sharing the same date table.

(1). Daily total sales YTD which includes total sales from beginning till end of year.

(2). Planned total sales which show total monthly sales only on the last day of every month.

The problem is I need to calculate the percentage of total sales at a given day compared to total planned monthly sales of that month using 1 slicer which is the date table (showing all date from beginning till end of year). 

Please have a look at the example:

Date            | Daily Sales | Monthly Plan (What I wanted)

11/01/2022 | 500            | 10.000
11/02/2022 | 459            | 10.000
11/03/2022 | 400            | 10.000
...                 |    ...             |    ...
12/01/2022 | 600            | 12.000
12/02/2022 | 550            | 12.000
...                 |   ...              |    ...

What I wanted to show is when I choose a random date from the date slicer, It would show the % of total sale from beginning of the month upto that date versus that month's sales plan.

Appreciate your help alot! Thanks!

[amitchandak · Accepted solution]

@hieudm6 , You can show complete month data like

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[hieudm6]

Hi @amitchandak,

Thank you for your help, I tried the function but it seemed to aggregate total plan amount of 12 months and display regardless of which month it was.

Date            | Daily Sales | Monthly Plan (What I wanted)  | Monthly Plan (What I tried) --> Annual total planned
11/01/2022 | 500            | 10.000                                       | 150.000 
11/02/2022 | 459            | 10.000                                       | 150.000
11/03/2022 | 400            | 10.000                                       | 150.000
...                 |    ...             |    ...                                            | ...
12/01/2022 | 600            | 12.000                                       | 150.000
12/02/2022 | 550            | 12.000                                       | 150.000
...                 |   ...              |    ...                                            | ...

[v-jingzhang · Accepted solution]

Hi @hieudm6 

Not sure if I understand it correctly, you can download my sample file attached at bottom to see details. 

Best Regards,
Community Support Team _ Jing
If this post helps, please Accept it as Solution to help other members find it.

[hieudm6]

Thank you very much! It helps a lot.

[amitchandak · Accepted solution]

@hieudm6 , You can show complete month data like

This Month =
var _max = eomonth(if(isfiltered('Date'),MAX( 'Date'[Date]) , today()),0)
var _min = eomonth(_max,-1)+1 ,
return
CALCULATE([net] ,DATESBETWEEN('Date'[Date],_min,_max))

or

Power BI Distributing/Allocating the Monthly Target(Convert to Daily Target): Measure ( Daily/MTD): https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Monthly-Target-...

PowerBI Distributing/Allocating the Yearly Target(Convert to Daily Target): Measure ( Daily/YTD): Magic of CLOSINGBALANCEYEAR With TOTALYTD/DATESYTD: https://community.powerbi.com/t5/Community-Blog/Power-BI-Distributing-Allocating-the-Yearly-Target-C...

[hieudm6]

Thank you very much, I accidentally deleted the relationship between the date table and 1 off my dataset which caused the above problem. Your functions worked perfectly as well!